Search code examples
sqlgroup-bywhere-clausehaving

In the SQL Query I couldn't get truly data

enter image description here

Hi, I have a facilities table. I want to know which hotel_id has 2 facilities at the same time. 

SELECT * 
from hotel_facilities_has 
WHERE isHas='1' AND (facilities_id=1 AND facilities_id=2) 
GROUP BY hotel_id

with that no record.

Solution

  • Your query does not work because you are looking for rows that satisify both conditions simultaneously, which cannot happen. You need to look across rows of the same group.

    Instead, you can filter on rows that satisfy either condition, and then ensure that you do have two rows per group:

    select hotel_id
from mytable
where isHas = 1 and facilities_id in (1, 2)  -- one or the other
group by hotel_id 
having count(*) = 2                          -- two rows in the group

    If (hotel_id, facilities_id ) tuples are not unique, you need having count(distinct facilities_id) = 2 instead.