Query to link two table filtering out records

blackbox

Hi all,

I have two tables, Room_Valid and Room_Avail. The Room_Valid table holds valid room numbers. The Room_Avail table holds codes for room availability.

The query below returns all the records from Room_Valid along with any Room_Avail codes if a room number in Room_Valid matches a room number in Room_Avail.

SELECT DISTINCT
Room_Valid.Room AS RoomNo,
Room_Avail.Avail AS Comp,
Room_Avail.UniqueID
FROM
Room_Valid
Left Outer Join Room_Avail ON Room_Valid.Room = Room_Avail.Room
WHERE
Room_Valid.HotelID = 'LON'
ORDER BY Room_Valid.Room ASC

What I would like to do is return all the records from Room_valid if no match is found in Room_Avail.

I am slightly stuck on how to construct the query.

Can you help.

Many thanks in advance.

bradgrafelman

Sounds like one way to do what you're after is to use a "WHERE NOT EXISTS" with a subquery on the Room_Avail table.

NogDog

bradgrafelman;11033381 wrote:
Sounds like one way to do what you're after is to use a "WHERE NOT EXISTS" with a subquery on the Room_Avail table.

johanafm

blackbox;11033379 wrote:
The query below returns all the records from Room_Valid along with any Room_Avail codes if a room number in Room_Valid matches a room number in Room_Avail.

No, the query returns what you describe along with any records from room_valid that has no match in room_avil.

blackbox;11033379 wrote:
What I would like to do is return all the records from Room_valid if no match is found in Room_Avail.

Which you already have in your result. If you wish to have ONLY these rows, then specify that with a where clause (hint, outer joins will fill in nulls for fields from the joined table when there are no matching rows)

bradgrafelman;11033381 wrote:
Sounds like one way to do what you're after is to use a "WHERE NOT EXISTS" with a subquery on the Room_Avail table.

Certainly one way, but WHERE (not) EXISTS queries are run once per row in the outer query and thus terribly inefficient. And since the data is allready pulled out in the first query anyway, he might just as well just narrow it down to leave what he wants.

bradgrafelman

johanafm;11033395 wrote:
Certainly one way, but WHERE (not) EXISTS queries are run once per row in the outer query and thus terribly inefficient.

Interesting, I hadn't thought about that. Would perhaps a better way be to do a LEFT JOIN with a WHERE condition that checks if the Room_Avail column IS NULL?