OUTER 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

SQL Server 2005 Forums

Transact-SQL (2005)

OUTER JOIN

Author

Topic

r4
Starting Member

6 Posts

Posted - 2010-09-22 : 14:13:11

I'd like to get all records from one table and the records that are common to both. On the right table needs a where clause and that where clause results in not all records from the left table. I've tried several things (i.e unions), but without luck. Here's the code and rough example of what I'd like as results. Basically I'd like be able to report on the empty sites.

select
S.MapIdNumber [Site],
M.LastName [Member],
convert(varchar(10),(R.StartDate),101) [Start],
convert(varchar(10),(R.EndDate),101) [End],
R.StatusId [Status]
from Sites S
left outer join Reservations R on S.Id = R.SiteId, Members M
where R.MemberId = M.Id
and S.ResortId = 1
and R.StatusId = 31

Site # Member Start End Status
1
2 Hiney 9/15/2010 9/20/2010 31
2
3
4 Davis 9/16/2010 9/19/2010 31
5
6 Little 9/15/2010 9/18/2010 31
7
8 Parker 9/16/2010 9/19/2010 31
9 Stitt 9/15/2010 9/19/2010 31
10 Cyr 9/17/2010 9/20/2010 31

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-22 : 15:38:03

Hard to tell from your query and description what you want. What would really help us to help you is some sample data and expected output from that sample data. here is a link that might help you prepare that information:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Here is a guess though:

select 
	S.MapIdNumber [Site],
	M.LastName [Member], 
	convert(varchar(10),(R.StartDate),101) [Start],
	convert(varchar(10),(R.EndDate),101) [End],
	R.StatusId [Status]
from 
	Sites S 
left outer join 
	(
		SELECT *
		FROM Reservations R
		INNER JOIN Members M
		ON	R.MemberId = M.Id
		WHERE R.StatusId = 31 
	) AS R 
	ON S.Id = R.SiteId
WHERE
	S.ResortId = 1

r4
Starting Member

6 Posts

Posted - 2010-09-23 : 09:24:29

Thanks for the reply ... here's some more info.

Sites Table
Resort Id MapId
1 1 M1
1 2 M2
1 3 M3
1 4 M4
1 5 M5
1 6 M6
1 7 M7
1 8 M8
1 9 M9
1 10 M10
2 1 M1
2 2 M2
2 3 M3
2 4 M4
2 5 M5
2 6 M6

Members Table
Id Name
1 Hiney
2 Davis
3 Little
4 Parker
5 Stitt
6 Cyr

Reservation Table
Id MemberId Resort SiteId StartDate FinishDate Status
20 1 1 2 9/15/2010 9/20/2010 31
21 2 1 4 9/16/2010 9/19/2010 31
22 3 1 6 9/15/2010 9/18/2010 31
23 4 1 8 9/16/2010 9/19/2010 31
24 5 1 9 9/15/2010 9/19/2010 31
25 6 1 10 9/17/2010 9/20/2010 31

Expected Result
Site # Member Start End Status
M1 No camper
M2 Hiney 9/15/2010 9/20/2010 31
M3 No camper
M4 Davis 9/16/2010 9/19/2010 31
M5 No camper
M6 Little 9/15/2010 9/18/2010 31
M7 No camper
M8 Parker 9/16/2010 9/19/2010 31
M9 Stitt 9/15/2010 9/19/2010 31
M10 Cyr 9/17/2010 9/20/2010 31

The report could then be used by staff to check campsites.
(i.e. verify that site #'s M1,M3,M5,M7 are not occupied at resort #1)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 09:29:50

Not just this?:



SELECT
	s.[mapID] AS [Site]
	, m.[name] AS [Member]
	, r.[startDate] AS [Start]
	, r.[finishDate] AS [End]
	, r.[status] AS [Status]
FROM
	Sites AS s
	LEFT JOIN reservation AS r ON r.[siteID] = s.[ID]
	LEFT JOIN members AS m ON m.[ID] = r.[memberID]
ORDER BY
	s.[mapID] ASC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

r4
Starting Member

6 Posts

Posted - 2010-09-23 : 09:32:45

Note: I changed your query a bit to this ...

select
S.MapIdNumber [Site],
M.LastName [Member],
convert(varchar(10),(R.StartDate),101) [Start],
convert(varchar(10),(R.EndDate),101) [End],
R.StatusId [Status]
from
Sites S
left outer join
(
SELECT *
FROM Reservations R
INNER JOIN Members M
ON R.MemberId = M.Id
WHERE R.StatusId = 31
AND R.ResortId = 1 << Added
) AS R
ON S.Id = R.SiteId
WHERE
S.ResortId = 1

and got this message ...

Msg 8156, Level 16, State 1, Line 1
The column 'Id' was specified multiple times for 'R'.

r4
Starting Member

6 Posts

Posted - 2010-09-23 : 09:43:00

Charlie ... Thanks for the reply. I need to use the reservation status otherwise all the old reservations will be selected.
i.e R.StatusId = 31 --Checked In Status

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 09:51:56

Then:



SELECT
	s.[mapID] AS [Site]
	, m.[name] AS [Member]
	, r.[startDate] AS [Start]
	, r.[finishDate] AS [End]
	, r.[status] AS [Status]
FROM
	Sites AS s
	LEFT JOIN reservation AS r ON
		r.[siteID] = s.[ID]
		AND r.[statusId] = 31
	LEFT JOIN members AS m ON m.[ID] = r.[memberID]
ORDER BY
	s.[mapID] ASC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

r4
Starting Member

6 Posts

Posted - 2010-09-23 : 11:14:25

Charlie ... that is very close. I get the not occupied sites.
But I also am getting all sites twice.

SELECT
S.MapIdNumber AS [Site]
, (M.LastName + ', ' + M.FirstName) AS [Member]
, R.[StartDate] AS [Start]
, R.[EndDate] AS [End]
, R.TypeOfReservationStatusId AS [Status]
FROM
Sites AS S
LEFT JOIN Reservations AS R ON R.SiteID = S.ID
AND R.TypeOfReservationStatusId = 31
AND S.ResortId = 1
LEFT JOIN Members AS M ON M.Id = R.MemberId
ORDER BY
S.MapIdNumber ASC

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 11:33:58

bring back the primary keys of each table. This will show you where the data duplication / mutltiple rows are.

Regards,
Charlie,.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

r4
Starting Member

6 Posts

Posted - 2010-09-23 : 15:56:32

Since the results are exported to Excel I am just going to use Excel's Advanced Filter for unique rows.

Thanks all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources