sql - Join query with only columns that have all values in `in` clause

Question

I'm creating a simple filtering system for my website. I have a many to many relationship between venues and amenities. Here are my tables.

NOTE: all ids are uuids. Making them short for simplicity

venues:

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |
| 'bbb' | 'second venue' |
| 'ccc' | 'third venue'  |

amenities:

| id    |      name        |
___________________________
| 'aaa' |  'first amenity' |
| 'bbb' | 'second amenity' |
| 'ccc' | 'third amenity'  |

amenity_venue:

| amenity_id  |    venue_id  |
______________________________
| 'aaa'       |  'aaa'       |
| 'bbb'       | 'aaa'        |
| 'ccc'       | 'aaa'        |
| 'aaa'       | 'bbb'        |
| 'bbb'       | 'ccc'        |

I'm trying to write a query to return the venues that have at least all the passed in amenity_ids. For example passing in amenity_ids aaa and bbb.

Output I'm looking for when the amenity ids passed in are aaa and bbb.

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |

Initially I tried this query

select * from venues 
INNER JOIN amenity_venue ON amenity_venue.venue_id = venues.id
where amenity_id in ('aaa', 'bbb');

This returns all the venues that have either amenity_id aaa or bbb

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |
| 'bbb' | 'second venue' |
| 'ccc' | 'third venue'  |

so then naively I tried

select * from venues 
INNER JOIN amenity_venue ON amenity_venue.venue_id = venues.id
where amenity_id = 'aaa'
  and amenity_id = 'bbb';

Which returns nothing. I'm trying to write a query where if amenity_ids aaa and bbb are passed in only venue aaa is returned since its the only venue that has a relationship with both amenities. Also the number of amenities is dynamic from query to query.

Answer 1

I think you are looking for

SELECT v.*
FROM venues v
WHERE v.name IN (/* list of venues names */)
  AND NOT EXISTS (
         SELECT 1
         FROM amenities AS a
         WHERE a.name IN (/* list of amenity names */)
           AND NOT EXISTS (
                  SELECT 1
                  FROM amenity_venue AS av
                  WHERE av.venut_id = v.id
                    AND av.amenity_id = a.id
               )
      );

That should work independent of how many amenities there are.

You can add conditions in the places I indicated to limit the query to only a certain subset of amenities or venues.