SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Type of Join ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 01/09/2008 :  06:17:11  Show Profile  Reply with Quote
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)

Singapore
17598 Posts

Posted - 01/09/2008 :  06:21:26  Show Profile  Reply with Quote
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'
	



KH
Time is always against us

Go to Top of Page

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 01/09/2008 :  06:21:38  Show Profile  Reply with Quote
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)))
Go to Top of Page

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 01/09/2008 :  06:26:20  Show Profile  Reply with Quote
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'


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000