jeudi 8 janvier 2015

How to query all rows that contain ALL values of a given many to many relation? (Not only one of them!)

I've following database structure:



  • Table 1: Furniture (Fields: ID)

  • Table 2: Material (Fields: ID)

  • Table 3: MaterialMatching: (Fields: ID, FK_Furniture, FK_Material)


This structure is able to relate many materials with every furniture...


I know how to query all furniture, that have some special material where the logical relation between the materials is OR:



select distinct(furniture.ID) from Furniture
left join MaterialMatching ON MaterialMatching.FK_Furniture = Furniture.ID
left join Material On Material.ID = MaterialMatching.FK_Material
where Material.ID IN (<< material ids I want to query>>)


Question


How can I query all furniture that have ALL materials? I want to get all furnitures, that have Material 1 AND Material 2 AND ...


Aucun commentaire:

Enregistrer un commentaire