Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    2

    Lightbulb Unanswered: Need urgent help with a many to many link table query!

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    2
    Brilliant, it works well!

    Thanks very much for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •