Join Question -- sql server programming

sql server programming : Join Question

tshad
9/25/2007 5:59:16 PM

I am looking at joins and was curious about why this is happening.

I have 2 RIGHT Joins but only the last one seems to be happening.

If I have the following:

SELECT e.EmployeeID,e.EmployeeName,Department.DepartmentName,o.Name,s.Name
FROM Employee e
LEFT JOIN Department ON e.DepartmentID = Department.DepartmentID
RIGHT JOIN Offices o ON (e.OfficeID = o.OfficeID)
RIGHT JOIN Stores s ON (e.StoreID = s.StoreID)

I will get mainly nulls, but I will get all the Stores (4 of them)
displayed. But if I reverse it so that the Offices table is last:

EmployeeID EmployeeName DepartmentName Name Name
---------------- --------------------------------------------------
NULL NULL NULL NULL Store 1
NULL NULL NULL NULL Store 2
NULL NULL NULL NULL Store 3
NULL NULL NULL NULL Store 4

SELECT e.EmployeeID,e.EmployeeName,Department.DepartmentName,o.Name,s.Name
FROM Employee e
LEFT JOIN Department ON e.DepartmentID = Department.DepartmentID
RIGHT JOIN Stores s ON (e.StoreID = s.StoreID)
RIGHT JOIN Offices o ON (e.OfficeID = o.OfficeID)

I only get the Offices.
EmployeeID EmployeeName DepartmentName Name Name
---------------- --------------------------------------------------
NULL NULL NULL Office 1 NULL
NULL NULL NULL Office 2 NULL
NULL NULL NULL Office 3 NULL
NULL NULL NULL Office 4 NULL

I am curious as to why this is happening? I would have thought that I would
get all the Offices and all the Stores since they are both Right Joined.

Following are the tables and data.

Thanks,

Tom

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Employee
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Department]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Department
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Stores]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Stores
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Offices]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Offices
go

CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeName] [varchar] (50) NULL ,
[DepartmentID] [int] NULL ,
[StoreID] [int] NULL ,
[OfficeID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Department] (
[DepartmentID] [int] NOT NULL ,
[DepartmentName] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Stores] (
[StoreID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Offices] (
[OfficeID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
) ON [PRIMARY]
GO

INSERT Employee (EmployeeName,DepartmentID,StoreID,OfficeID)
SELECT 'Smith',1,102,NULL UNION ALL
SELECT 'Jack',2,100,NULL UNION ALL
SELECT 'Jones',2,NULL,405 UNION ALL
SELECT 'Andrews',3,102,NULL UNION ALL
SELECT 'Dave',5,NULL,402 UNION ALL
SELECT 'Joseph',NULL,102,104

INSERT Department (DepartmentID,DepartmentName)
SELECT 1,'HR' UNION ALL
SELECT 2,'Finance' UNION ALL
SELECT 3,'Security' UNION ALL
SELECT 4,'Sports' UNION ALL
SELECT 5,'HouseKeeping' UNION ALL
SELECT 6,'Electrical'

INSERT Offices (OfficeID,Name)
SELECT 402,'Office 1' UNION ALL
SELECT 403,'Office 2' UNION ALL
SELECT 404,'Office 3' UNION ALL
SELECT 405,'Office 4'

INSERT Stores (StoreID,Name)
SELECT 100,'Store 1' UNION ALL
SELECT 101,'Store 2' UNION ALL
SELECT 102,'Store 3' UNION ALL
SELECT 103,'Store 4'

Jim Underwood
9/26/2007 12:00:00 AM

Read over Tom's explanation of the join, to understand why the original code
didn't work.

I think what you want here is a FULL OUTER JOIN, but I'm not entirely
certain. The code below will always return one row for every employee, and
at least one row for every store and at least one row for every office. The
full outer join says "take every row from both tables". In this case, the
previous result set contains every employee with or without a department,
and the full outer join preserves this, adding in every Office that is not
referenced by an employee. The last outer join takes this whole set and
matches it to stores, adding in any stores that are not referenced by an
employee.

If you want to exclude rows where an employee does not reference any offices
or stores, add in "where e.employeeID is not null"

SELECT e.EmployeeID,e.EmployeeName,Department.DepartmentName,o.Name,s.Name
FROM Employee e
LEFT JOIN Department ON e.DepartmentID = Department.DepartmentID
full outer join Offices o ON (e.OfficeID = o.OfficeID)
full outer JOIN Stores s ON (e.StoreID = s.StoreID)

[quoted text, click to view]

Tom Cooper
9/26/2007 12:18:22 AM

Each Join is done one at a time. So you have

SELECT e.EmployeeID,e.EmployeeName,Department.DepartmentName,o.Name,s.Name
FROM Employee e
LEFT JOIN Department ON e.DepartmentID = Department.DepartmentID
RIGHT JOIN Offices o ON (e.OfficeID = o.OfficeID)
RIGHT JOIN Stores s ON (e.StoreID = s.StoreID)

So SQL Server takes each Employee row and matches that row to Department.
It will keep every combination that meets the ON condition. Since this is a
LEFT JOIN, if an Employee row does not have a match, that Employee row is
kept and all the Department columns are set to NULL. So at this point,
every Employee row will have at least one row in this intermediate result
set, but some or all of the Department rows may not have rows in the
intermediate result set.

Now SQL Server does the RIGHT JOIN Offices o ON (e.OfficeID = o.OfficeID).
So now it keeps every row from Offices and the matching row(s) from the
intermediate result set. Since this is a RIGHT JOIN, if an Offices row does
not have a match, that Offices row is kept and all the columns from the
first intermediate result set are set to NULL. So now, every row from
Offices will have at least one row in this second intermediate result set,
but some or all or the rows from the first intermediate result may not have
rows in the second result set.

Now SQL Server does the RIGHT JOIN Stores s ON (e.StoreID = s.StoreID). So
now it keeps every row from Stores and the matching row(s) from the second
intermediate result set. Since this is a RIGHT JOIN, if an Stores row does
not have a match, that Stores row is kept and all the columns from the
second intermediate result set are set to NULL. So now, every row from
Stores will have at least one row in this final result set, but some or all
or the rows from the second intermediate result may not have rows in this
result set. So while every row from Stores will have a row in the result
set, there is no guarentee that all (or any) of the rows will be in this
final result.

If you give us the DDL and DML to create some sample data, and the results
you would want from the query with that sample data (see www.aspfaq.com/5006
for how to do this), we can probably give you a query that does what you
want.

Tom

[quoted text, click to view]

Sylvain Lafontaine
9/26/2007 12:21:47 AM

First, you have made a little error, the line « SELECT
'Joseph',NULL,102,104 » should be « SELECT 'Joseph',NULL,102,404» instead
because there is no OfficeId with a value of 104.

You are making a common error in believing that the RIGHT OUTER JOIN is
simply the inverse of a LEFT OUTER JOIN. It is when you have only one join
but when you have multiple joins, the order of precedence for each join must
be taken into account. You think that you have something like a star
relationship:

O -- E -- D
|
S

but what you have instead is the following:

S --> (O --> ( E --> D))

and for the second Select:

O --> (S --> ( E --> D))

If you want a star relationship, simply replace your RIGHT JOIN with LEFT
JOIN:

SELECT e.EmployeeID,e.EmployeeName,Department.DepartmentName,o.Name,s.Name
FROM Employee e
LEFT JOIN Department ON e.DepartmentID = Department.DepartmentID
LEFT JOIN Offices o ON (e.OfficeID = o.OfficeID)
LEFT JOIN Stores s ON (e.StoreID = s.StoreID)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

[quoted text, click to view]

Tom Cooper
9/26/2007 12:52:05 AM

You DID provide the DDL and DML. My apologies for missing it and thank you
for providing it. I's still not sure exactly what result you would want
from the data you gave us. But here's one guess at what you want:

SELECT e.EmployeeID,e.EmployeeName,Department.DepartmentName,o.Name,s.Name
FROM Offices o
CROSS JOIN Stores s
LEFT JOIN Employee e ON e.OfficeID = o.OfficeID OR e.StoreID = s.StoreID
LEFT JOIN Department ON e.DepartmentID = Department.DepartmentID

If that doesn't give you what you want, please provide the results you would
want with your sample data.

Tom

[quoted text, click to view]

David Portas
9/26/2007 7:27:17 AM

[quoted text, click to view]

A suggestion: if you stick to using LEFT OUTER JOIN every time instead
of mixing the LEFT and RIGHT syntax you may find the meaning becomes
clearer. Understand that the LEFT and RIGHT versions differ only in
their syntax, not in their end results. There is no need to use both.

--
David Portas

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008

home · blog · groups

Questions? Comments? Contact the dn