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?

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

On Jul 8, 4:59*pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> 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

(jwwarrenva@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, esquel@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