Need urgent help with a many to many link table query!

abc991 · #1 (**permalink**) 02-26-11, 08:25

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

r937 · #2 (**permalink**) 02-26-11, 08:35

to find which rooms have those facilities...

Code:

SELECT room_id
  FROM roomfacilites
 WHERE facility_id IN ( 1,5,6 )
GROUP
    BY room_id
HAVING COUNT(*) = 3

to display room data in a single query...

Code:

SELECT rooms.id
     , rooms.room_code
     , rooms.capacity
  FROM ( SELECT room_id
           FROM roomfacilites
          WHERE facility_id IN ( 1,5,6 )
         GROUP
             BY room_id
         HAVING COUNT(*) = 3 ) AS r
INNER
  JOIN rooms
    ON rooms.id = r.room_id

abc991 · #3 (**permalink**) 02-26-11, 08:45

Brilliant, it works well!

Thanks very much for your help!

Internet Services

Web Development

Digital Marketing

Consumer Tech