Type of Join ? - SQL Server Forums

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

Type of Join ?

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-09 : 06:17:11

I have a table tblRooms and a table tblBookings. They are joined by RoomRef. There may or may not be a record on tblBookings for a room. I want my view to list all the rooms and show a blank line if there is no entry in tblBookings for that room. I've tried changing the join to lis all records from tbl rooms but it doesn't work.

SELECT *
FROM dbo.tblRB_Bookings RIGHT OUTER JOIN
dbo.tblRB_Rooms ON dbo.tblRB_Bookings.BK_RoomRef = dbo.tblRB_Rooms.RM_RoomRef
WHERE (dbo.tblRB_Bookings.BK_DateRequired = CONVERT(DATETIME, '2008-01-10 00:00:00', 102))

eg

Room1 Booking 1.00 to 2.00
Room2
Room3 Booking 11.30 to 12.30
Room3 Booking 3.00 to 4.30
Room4
Room5 Booking 10.00 to 3.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-09 : 06:21:26

[code]SELECT *
FROM dbo.tblRB_Bookings
RIGHT OUTER JOIN
dbo.tblRB_Rooms
ON dbo.tblRB_Bookings.BK_RoomRef = dbo.tblRB_Rooms.RM_RoomRef
AND dbo.tblRB_Bookings.BK_DateRequired = '20080110'

OR

SELECT *
FROM dbo.tblRB_Rooms
LEFT OUTER JOIN
dbo.tblRB_Bookings
ON dbo.tblRB_Bookings.BK_RoomRef = dbo.tblRB_Rooms.RM_RoomRef
AND dbo.tblRB_Bookings.BK_DateRequired = '20080110'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-01-09 : 06:21:38

Try :---

SELECT *
FROM dbo.tblRB_Bookings RIGHT OUTER JOIN
dbo.tblRB_Rooms ON (dbo.tblRB_Bookings.BK_RoomRef = dbo.tblRB_Rooms.RM_RoomRef
AND (dbo.tblRB_Bookings.BK_DateRequired = CONVERT(DATETIME, '2008-01-10 00:00:00', 102)))

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-09 : 06:26:20

Thank you khtan - this did what i wanted

SELECT *
FROM dbo.tblRB_Rooms
LEFT OUTER JOIN
dbo.tblRB_Bookings
ON dbo.tblRB_Bookings.BK_RoomRef = dbo.tblRB_Rooms.RM_RoomRef
AND dbo.tblRB_Bookings.BK_DateRequired = '20080110'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources