Re: SQL query to return all matching condition
Is there a change the plan would change if you write it like this:
SELECT DISTINCT a.*
FROM shelter a
INNER JOIN (
SELECT c.serial_shelter
FROM shelter_location c
WHERE c.Location_Category
IN (
'Cake shop', 'Other Store'
)
GROUP BY c.serial_shelter
HAVING count( DISTINCT c.location_category ) = 2
) s
ON a.Shelter_Code = s.serial_shelter
But the query you send the plan for selects both a.* and a.something, so just, a.* should suffice. doesn't it?
Also, you joined to shelter_location b, but it is otherwise never used, and as you are already constraining the result of shelter a with the values from the subquery, I really don't see how the join to shelter b could contribute.
If I'm off track here, please tell...