Need help with a SQL statement in a link table

[mike2325]

Hi,

 

I have a link table with the values Room_Code and Facility_ID from a Room table and a facility table respectively. The Room table contains fields Room_Code, Capacity etc and the Facility table contains Facility_ID and Facility_Type.

 

Therefore the link table for example contains:

 

room code: abc1    Facility_ID:1

room code: abc1    Facility_ID:5

room code: abc1    Facility_ID:6

room code: abc1    Facility_ID:9

room code: jjj1    Facility_ID:1

room code: jjj1    Facility_ID:2

room code: jjj1    Facility_ID:7

room code: jjj1    Facility_ID:9

 

I wanted to write a sql statement so that the user could select a room that has the Facility_IDs 1, 5 and 6 - so as to search for rooms with those facilities.  Any ideas on how to do this as I am confused with searching between the different records.

 

Thanks,

Mike

 

[fenway]

I just posted a solution like this a few days ago -- use a cross join for each condition you want to match.