Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Display a certain result if two items exist?

Author  Topic 

Account
Starting Member

1 Post
Posted - 2010-05-13 : 21:27:12
The subject title might be confusing, but please hear me out.

I have two tables, one called Rooms and one called RoomsFacility.
This table is Rooms:
http://i41.tinypic.com/aneexs.png

The RoomsFacility has a Number for every facility (e.g. 1 is a facility, 2 is a facility etc). For every one facility for a room, a row is added. So if a room consists of 3 facilities, the table would have 3 rows, like so:
http://i41.tinypic.com/2jb8ax2.png

What I'm trying to do is run a query so that they join together, and for example I wanted a room with facility 1 AND 2, it will show the results of the rooms with 1 and 2 (and anymore if there is).



It's really doing my head in, and I know the answer is simple. I've been trying something like:
SELECT DISTINCT Rooms.Room_Number, Rooms.Park, Rooms.Building, Rooms.Capacity
FROM Rooms INNER JOIN RoomFacility ON Rooms.Room_Number = RoomFacility.Room_Number
WHERE(RoomFacility.Facility_ID = '1') AND
(RoomFacility.Facility_ID = '2')

But no results show up.

Can anyone help me please? Thanks.

malpashaa
Constraint Violating Yak Guru

264 Posts
Posted - 2010-05-14 : 05:16:17
Try this:

SELECT R.Room_Number, R.Park, R.Building, R.Capacity
  FROM Rooms AS R
       INNER JOIN
       RoomFacility AS RF
       ON R.Room_Number = RF.Room_Number
 WHERE RF.Facility_ID = '1'
    OR RF.Facility_ID = '2'
 GROUP BY R.Room_Number, R.Park, R.Building, R.Capacity
HAVING COUNT(DISTINCT RF.Facility_ID) = 2
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site