SQL problem - a variation on outer join

[jwwarrenva]

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?

[Plamen Ratchev]

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

[jwwarrenva]

On Jul 8, 4:59*pm, "Plamen Ratchev" <[e-mail]:

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

[Erland Sommarskog]

(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