SQL Select based on a single field

The following is the structure of database table consisiting of foreign keys from two different tables.

         facilityID     roomID
            1           A.0.0.1
            2           A.0.0.1
            1           B.0.0.1
            2           B.0.0.1
            3     开发者_JAVA技巧      B.0.0.1

What I would like to do is perform a select statement something like

         Select roomID from room_facility where facilityID = '1' and facilityID = '3'

so that it gives me back a room which has both facilities combined in this case roomID "B.0.0.1" but the above SQL returns empty table. I have tried using 'OR' instead of 'AND' but but that gives me roomID "A.0.0.1" and "B.0.0.1" both which isn't what I am looking for because room "A.0.0.1" doesn't have facility "3".

I don't know if I am making any basic fundamental mistake over here but any guidance would be very helpful whether it is with SQL, a way to do it in SQL Server Stored Procedure or redesigin of the table's.

Thanks.

A where clause is evaluated for every row. No row can satisfy facilityID = '1' and facilityID = '3' at the same time.

One approach is a group by. You can use count(distinct facilityID) to ensure both rooms are found:

select  roomID
from    room_facility
where   facilityID in ('1','3')
group by
        roomID
having  count(distinct facilityID) = 2

This is a Relational Division problem.

SELECT roomID
FROM   room_facility
WHERE  facilityID IN ( '1', '3' )
GROUP  BY roomID
HAVING COUNT(*) = 2  /*Assuming facilityID,roomID is a composite PK*/