469,591 Members | 1,992 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,591 developers. It's quick & easy.

SQL problem - a variation on outer join

All,

I am far from expert with SQL and have been unable to solve the
following problem (stated in generic terms):

I have three tables:
1. Employees - contains EmployeeName
2. Years - contains Year
3. Earnings - contains Year, EmployeeName, and Earnings

I need a query that will return a row with the Year, EmployeeName, and
Earnings for every possible combination of Year and EmployeeName, even
if that combination does not appear in the Earnings table.

If it matters, I am using SQL Server 2005.

Can you help me?
Jul 8 '08 #1
3 1910
You need to CROSS JOIN the Employees and Years tables to get all possible
combinations and then do a LEFT JOIN to the Earnings table:

SELECT E.EmployeeName,
Y.[Year],
M.Earnings
FROM Employees AS E
CROSS JOIN Years AS Y
LEFT OUTER JOIN Earnings AS M
ON E.EmployeeName = M.EmployeeName
AND Y.[Year] = M.[Year];

This will show Earnings as NULL for non-matching employee/year. You can use
COALESCE(M.Earnings, 0) if you need it as 0.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jul 8 '08 #2
On Jul 8, 4:59*pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
You need to CROSS JOIN the Employees and Years tables to get all possible
combinations and then do a LEFT JOIN to the Earnings table:

SELECT E.EmployeeName,
* * * * * Y.[Year],
* * * * * M.Earnings
FROM Employees AS E
CROSS JOIN Years AS Y
LEFT OUTER JOIN Earnings AS M
* ON E.EmployeeName = M.EmployeeName
*AND Y.[Year] = M.[Year];

This will show Earnings as NULL for non-matching employee/year. You can use
COALESCE(M.Earnings, 0) if you need it as 0.

HTH,

Plamen Ratchevhttp://www.SQLStudio.com
Thanks, Plamen, that was exactly what I needed.

John Warren
Jul 9 '08 #3
(jw********@gmail.com) writes:
I am far from expert with SQL and have been unable to solve the
following problem (stated in generic terms):

I have three tables:
1. Employees - contains EmployeeName
2. Years - contains Year
3. Earnings - contains Year, EmployeeName, and Earnings

I need a query that will return a row with the Year, EmployeeName, and
Earnings for every possible combination of Year and EmployeeName, even
if that combination does not appear in the Earnings table.

If it matters, I am using SQL Server 2005.
SELECT E.EmployeeName, Y.Year, Ea.Earnings
FROM Years Y
CROSS JOIN Employees E
LEFT JOIN Earnings Ea ON Ea.Year = Y.Year
AND Ea.EmployeeName = E.EmployeeName
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 10 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Martin | last post: by
8 posts views Thread by Matt | last post: by
3 posts views Thread by Martin | last post: by
1 post views Thread by Andreas Bauer | last post: by
5 posts views Thread by Eitan M | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.