Intersection in single table

Question

I have a table structure like this:

| id | itinerary_id | destination_id |

I have to fetch itinerary_id, which exists with multiple destination_id, with using AND condition. For example itinerary_id 1 is mapped with destination_id 1,2,3, whereas 2 is mapped with destination_id 2, and 3 is mapped with destination_id 3. So, when I use filter with destination 1,2 and 3, result will be 1 only.

Suggest how I can write query for such situation, please.

Answer 1

Try this one:-

SELECT itinerary_id 
FROM tab
WHERE destination_id in (1,2,3)
GROUP BY itinerary_id
HAVING count(*)=3;