Sélectionnez où et a la même colonne

Vous pouvez utiliser l'agrégation pour obtenir le

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)
      );

Vous pouvez utiliser count (*) s'il n'y a pas de doublons dans le tableau.

Si vous voulez les lignes originales (plutôt que juste les lieux), alors je suggérerais exists:

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

La requête ci-dessous est évolutive, dans le cas où un nouvel amenity_id est ajouté.

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 
    )
)

Une variante de la réponse de Gordon:

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

Les différences essentielles:

Fonctionne pour n'importe quel nombre d'identifiants d'agrément que vous mettez dans VOTRE LISTE ICI

Renvoie une sortie au format que vous avez demandé

Comment ça marche:

Vous voulez connaître tous les lieux qui ont chacun des équipements répertoriés

Nous réduisez le tableau amenity_venue à une simple liste de toutes les commodités qui nous intéressent (dans votre cas, 1 et 2). Maintenant, nous voulons savoir quels sites ont chaque équipement différent.

Nous faisons cela en comptant le nombre d'équipements dont dispose un lieu et en exigeant qu'il soit égal au nombre d'équipements distincts dans la liste. C'est le but du groupe en / ayant- il compte les équipements distincts par lieu et le compare au nombre distinct d'équipements dans la liste entière.

Le CTE contient une jointure à gauche qui produit une liste de ID d'agrément et lieux qui ont cet ID, mais de manière critique, il produit également un ID d'agrément associé à NULL si aucun lieu n'a cet équipement. Si nous avons donc exigé qu'un lieu ait des commodités 1,2,3 alors cela signifie que la liste distincte des commodités dans le CTE est de trois et cette requête: ( (select count (distinct amenity_id) from cte) ) produit un décompte de 3. Cependant, rappelez-vous qu'aucun lieu ne possède l'agrément 3, donc le lieu le plus adapté (1) a les aménagements 1 et 2, le GROUP BY / HAVING regroupe sur l'identifiant du lieu et produit un décompte de 2 aménagements. Parce que 3 2, cela signifie que notre demande de "lieux qui ont 1, 2, 3 commodités" ne produit aucun résultat. Si nous n'avions pas cette jointure de gauche et que nous nous appuyions uniquement sur les équipements attribués aux sites, demander 1, 2, 3, produirait une liste de sites qui ne disposent que des équipements 1 et 2, et cette paire serait comptée distinctement. à 2 (devrait être 3) et la requête donnerait de mauvais résultats

Après qu'une liste d'ID de lieu soit dérivée de ce processus, l'ID de lieu est joint à amenity_venue pour récupérer la sortie demandée