Blauhirn Blauhirn - 4 months ago 25
SQL Question

HAVING row exist

Following database sample

id | date | customer | ...
-----------------------------------------
1 2016-07-05 1
2 2016-07-05 2
3 2016-07-06 1
4 2016-07-07 1
5 2016-07-07 2


I want to select all customers which have an entry at
2016-07-07
, but not at
2016-07-06
.

At first, I thought I'd do this using
WHERE
:

SELECT * FROM table
WHERE EXISTS ( SELECT * FROM table WHERE date = '2016-07-07' )
AND NOT EXISTS ( SELECT * FROM table WHERE date = '2016-07-06' )
GROUP BY customer


but since
WHERE
is executed before
GROUP BY
, the result can only by empty - there IS a record in
table
at
2016-07-06
.

So, using
HAVING
, how would I do this? I am having difficulties checking for row existance in a
HAVING
clause. Something like:

SELECT * FROM table
GROUP BY customer
HAVING exists date '2016-07-07'
AND not exists date '2016-07-06'

Answer

Group by the customer and take only those groups having at least one date entry for 2016-07-07 and none for 2016-07-06

SELECT customer 
FROM your_table
GROUP BY customer
HAVING sum(date = '2016-07-07') > 0
   AND sum(date = '2016-07-06') = 0
Comments