Select where in and has same column

Question

I have the following table

amenity_venue (ids are uuids, made them ints for simplicty)
--------------------------
| amenity_id | venue_id  |
--------------------------
|     1      |     1     |
|     2      |     1     |
|     1      |     2     |
|     1      |     3     |

I'm trying to write a query where I select by amenity_id but only return results if the venue_id has both amenity_ids.

This is broken I know but something like:

select *
from amenity_venue where amenity_id in (1, 2)
having amenity_venue.venue_id = amenity_venue.venue_id

My query should only return

--------------------------
| amenity_id | venue_id  |
--------------------------
|     1      |     1     |
|     2      |     1     |

since venue 1 is the only venue that has both amenity_id 1 and 2. How could I write such a query?

Answer 1

You can use aggregation to get the

select venue_id
from amenity_venue
where amenity_id in (1, 2) 
group by venue_id
having count(distinct amenity_id) = 2;  -- this is the number of values in the `in` list

You can use count(*) if there are no duplicates in the table.

If you want the original rows (rather than just the venues), then I would suggest exists:

select av.*
from amenity_venue av
where (av.amenity_id = 1 and
       exists (select 1 from amenity_venue av2 where av2.venue_id = av.venue_id and av2.amenity_id = 2)
      ) or
      (av.amenity_id = 2 and
       exists (select 1 from amenity_venue av2 where av2.venue_id = av.venue_id and av2.amenity_id = 1)
      );

Answer 2

The below query is scalable, in the case where a new amenity_id is added.

SELECT *
FROM amenity_venue
WHERE venue_id IN 
(
    --Get venue_id where all amenity_id exist
    SELECT venue_id 
    FROM amenity_venue
    GROUP BY venue_id
    HAVING count(*) = 
    (
        -- Get number of unique amenity_id
        SELECT count(DISTINCT amenity_id) FROM amenity_venue 
    )
)

Answer 3

A variation on Gordon's answer:

WITH cte AS
(
  select a.id as amenity_id, av.venue_id 
  from amenity a LEFT JOIN amenity_venue av ON a.id = av.amenity_id
  where a.id in (1,2)
)

SELECT av.*
FROM
  amenity_venue av
  INNER JOIN
  (
    SELECT venue_id
    FROM cte
    GROUP BY venue_id
    HAVING count(distinct amenity_id) =
    (select count(distinct amenity_id) from cte)
  ) x
  ON av.venue_id = x.venue_id

The essential differences:

Works for any number of amenity ids you put in YOUR LIST HERE

Returns an output in the format you requested

How it works:

You want to know all the venues that have every one of the amenities listed

We reduce the table amenity_venue to just a list of all the amenities we are interested in (in your case, 1 and 2). Now we want to know which venues have every single different amenity

We do this by counting the number of amenities a venue has and demanding it be equal to the number of distinct amenities in the list. This is the purpose of the group by/having- it counts the distinct amenities per venue and compares it to the distinct count of amenities in the whole list.

The CTE contains a LEFT JOIN that produces a list of amenity id and venues that have that ID, but critically it also produces an amenity ID paired with NULL if no venues have that amenity. If we thus demanded that a venue have amenities 1,2,3 then it means that the distinct list of amenities in the CTE is three and this query: ((select count(distinct amenity_id) from cte)) produces a count of 3. However, remember that no venue has amenity 3, so the most amenable venue (1) has amenities 1 and 2, the GROUP BY/HAVING groups up on venue ID, and produces a count of 2 amenities. Because 3 <> 2, it means our demand of "venues that have 1,2,3 amenities" produces no results. If we didn't have this left join, and purely relied on the amenities assigned to venues, then asking for 1,2,3, would produce a list of venues that have just amenities 1 and 2, and this pair would be distinct counted to be 2 (should be 3) and the query would deliver wrong results

After a list of venue ids is derived from this process the venue id is joined back to amenity_venue to retrieve the requested output