aero aero - 2 months ago 6
SQL Question

SQL GROUP BY "HAVING" the required rows

Is there a succinct way of using

HAVING
to check if the required rows are within the
GROUP BY
?

With the example date:

turtle_id name
1 Mike
2 Ralph
3 Leo
4 Don


and

turtle_id crush_for
1 Pizza
1 April Oneil
2 April Oneil
3 Pizza
3 April Oneil
4 Pizza
4 Pizza
4 Science
4 April Oneil


And the SQL:

SELECT turtle.name
FROM turtle
JOIN turtle_crush ON turtle_crush.turtle_id = turtle.turtle_id
WHERE turtle_crush.crush_for IN ('Pizza', 'April Oneil')
GROUP BY turtle.turtle_id
HAVING (a crush on both "Pizza" and "April Oneil")


I realize I could do something like
HAVING COUNT(*) > 1
, but that would give a false positive for Don (id 4) because he likes 'Pizza' twice.

Edit:
Just adding a
WHERE
clause will return Ralph where he doesn't have a
crush_for
'Pizza'

Answer

This should work:

SELECT t.turtle_name
FROM turtle t
INNER JOIN (SELECT turtle_id
            FROM turtle_crush
            WHERE crush_for IN ('Pizza','April Oneil')
            GROUP BY turtle_id
            HAVING COUNT(DISTINCT crush_for) = 2) tc
    on t.turtle_id = tc.turtle_id;

In this code, the subquery first filter the results where crush_for is either 'Pizza' or 'April Oneil'. Then it groups by turtle_id, with another condition of choosing those turtle_ids that have 2 different crush_for values (hence ensuring that you get only the turtle_ids that have both crushes). Then it's joined with the turtle table to get the name.

Comments