SQL Group by only if it satisfy a condition

Question

I am trying to query few users from a table joined to a bookings table but there are conditions to satisfy and I don't know how to get the expected results.

Conditions

Return unique users
Only if lasts 3 completed bookings are eq to notified is null

Tables

Users

id | name
---+-----
1  | Jonh
2  | Jim
3  | Jen

Bookings

id | user_id |    state    |        notified
---+---------+-------------+----------------------
12 | 1       | 'completed' | NULL
11 | 2       | 'completed' | NULL
10 | 3       | 'completed' | '2018-01-01 00:00:00'
9  | 1       | 'completed' | '2018-01-01 00:00:00'
8  | 2       | 'completed' | NULL
7  | 3       | 'completed' | NULL
6  | 1       | 'completed' | '2018-01-01 00:00:00'
5  | 2       | 'completed' | NULL
4  | 3       | 'completed' | NULL
3  | 1       | 'completed' | '2018-01-01 00:00:00'
2  | 2       | 'completed' | '2018-01-01 00:00:00'
1  | 3       | 'completed' | NULL

Query a have so far

SELECT users.id, bookings.id, bookings.notified
FROM users
  JOIN bookings ON users.id = bookings.user_id
  WHERE bookings.state = 'completed'
GROUP BY users.id, bookings.id, bookings.notified
HAVING (bookings.notified IS NULL)
ORDER BY bookings.id DESC;

Fiddle LINK

Expected

user_id 
-------
2

@GordonLinoff I am expecting to get user id 2 as the result that is the only one that satisfies my filtering. From the last user completed bookings the range of last 3 should be notified = null — Thiago Augustus Oliveira
– Thiago Augustus Oliveira, Commented Apr 11, 2018 at 15:20
I don't understand this requirement "Only if lasts 3 completed bookings are eq to notified is null". The last 3 means that they are comparable. But because they are NULL, they cannot be compared, and will typically come at the bottom of a ORDER BY bookings.notified DESC. By being NULL, they can never be among the last. — luis.espinal
– luis.espinal, Commented Apr 11, 2018 at 15:25
last three rows for user_id=3 has notified = null, why don't you expect user_id = 3? — McNets
– McNets, Commented Apr 11, 2018 at 15:44

Gordon Linoff · Accepted Answer · 2018-04-11 17:02:31Z

2

The following query returns the users that you want:

SELECT b.user_id
FROM bookings b
WHERE b.id >= (SELECT b2.id
               FROM bookings b2
               WHERE b2.user_id = b.user_id AND b2.state = 'completed'
               ORDER BY b2.id DESC
               OFFSET 2 FETCH FIRST 1 ROW ONLY
              ) AND
     b.state = 'completed'
GROUP BY b.user_id
HAVING COUNT(notified) = 0;

Here is the SQL Fiddle. Note this SQL Fiddle uses Postgres not MySQL (yours uses MySQL).

edited Apr 11, 2018 at 17:02

answered Apr 11, 2018 at 15:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

luis.espinal Over a year ago

My thoughts exactly. The requirement needs a subquery - it cannot be done in a single shot.

luis.espinal Over a year ago

Though, I do not follow what the OP means by this: "Only if lasts 3 completed bookings are eq to notified is null". Unless if by "last", he means the last three physical insertions and not a chronological "last" (which would not make sense for rows with the date field being NULL.

Thiago Augustus Oliveira Over a year ago

@luis.espinal I guess I didn't explain it right. What I mean is last 3 user bookings in a row. I am trying to get all users that didn't notify his book ending but I don't want to get those users that didn't notify his booking in his history, but only if his last 3 bookings he didn't notify the company. if I apply null into a where it will ignore those bookings between nulls. I would like to have only if there is sequential of nulls and this sequential is equal user last bookings

Collectives™ on Stack Overflow

SQL Group by only if it satisfy a condition

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related