473,372 Members | 1,063 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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 2077
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Martin | last post by:
I am now working on SQL Server 2000 having had previous experience on a different database. Both of the OUTER JOIN syntaxes is different from what I am used to and I am finding it slightly...
8
by: Matt | last post by:
Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and...
4
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
3
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2...
3
by: deko | last post by:
From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite... SELECT qryTxToQ1.Q1Total, qryTxToQ2.Q2Total,...
14
by: mike | last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly. There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key of Item, and is also a field in LogEvent. Some...
1
by: Andreas Bauer | last post by:
Hi, when I try a left outer join on one table everything works fine: select * from (tourist.users u left outer join tourist.user_extended_info ue on (u.id = ue.id)) But now I need to do...
5
by: Eitan M | last post by:
Hello, How can I do outer join in ms-access database ? Thanks :)
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.