Question on Joint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Question on Joint

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-11-08 : 15:17:06

Hi,

I have the following tables:

CREATE TABLE tblUser (userID smallint PRIMARY KEY IDENTITY NOT NULL, username nvarchar (20) NOT NULL);

INSERT INTO tblUser VALUES ('Peter');
INSERT INTO tblUser VALUES ('Paul');
INSERT INTO tblUser VALUES ('Mary');

CREATE TABLE tblPayDay (paydayID smallint PRIMARY KEY IDENTITY NOT NULL, userID smallint, intYear smallint, intMonth smallint, amount decimal);

INSERT INTO tblPayDay VALUES (1, 2006, 9, 10.00);
INSERT INTO tblPayDay VALUES (1, 2006, 10, 9.00);
INSERT INTO tblPayDay VALUES (1, 2006, 11, 12.00);
INSERT INTO tblPayDay VALUES (2, 2006, 10, 12.00);

Now I would like to join the 2 tables and find out everybody's pay in Nov 2006. I need to show all users, including those that don't get paid in the month and just display NULL in the pay amount column. This is my query:

SELECT u.username, p.intYear, p.intMonth, p.amount
FROM dbo.tblPayDay p
RIGHT OUTER JOIN dbo.tblUser u ON p.userID = u.userID
WHERE (p.intYear = 2006 OR p.intYear IS NULL)
AND (p.intMonth = 11 OR p.intMonth IS NULL)

It shows Mary alright, but the problem is that it isn't showing Paul with NULL pay. I know I can use UNION to resolve this but my "real-life" query actually is checking several other conditions too and if I need to use UNION, I will have to union all possible combinations of these conditions.

Any suggestion on how I can resolve this without using UNION?

Thanks,
ywb

Benholio
Starting Member

4 Posts

Posted - 2006-11-08 : 15:43:14

If you remove your WHERE clause to get an idea of what the join is returning, you will see that there are no entries for PAUL that meet your WHERE clause criteria.

Your query without the WHERE clause returns:

Peter 2006 9 10
Peter 2006 10 9
Peter 2006 11 12
Paul 2006 10 12
Mary NULL NULL NULL

The row for Paul doesn't have p.intMonth = 11 OR p.intMonth IS NULL.

Try moving your WHERE clause to the join, and you will get the NULLs for Paul and Mary.

SELECT u.username, p.intYear, p.intMonth, p.amount
FROM dbo.tblPayDay p
RIGHT OUTER JOIN dbo.tblUser u ON p.userID = u.userID and 
  (p.intYear = 2006 OR p.intYear IS NULL) 
   and (p.intMonth = 11 OR p.intMonth IS NULL)

output:

Peter	2006	11	12
Paul	NULL	NULL	NULL
Mary	NULL	NULL	NULL

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-11-08 : 15:51:53

Yeah! That works!

Thanks, Benholio!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-11-08 : 15:55:45

You simply select from all users and left outer join to your pay data, but put the criteria on the JOIN condition.

SELECT u.username, p.intYear, p.intMonth, p.amount
FROM dbo.tblUser u
LEFT OUTER JOIN dbo.tblPayDay p
ON p.userID = u.userID AND p.intYear = 2006 and p.intMonth = 11

Avoid RIGHT OUTER JOINS -- they make your SQL harder to read and troubleshoot, as you have seen.
Also, try to avoid hungarian notation for your column names and table names -- it is completely unncessary, makes your code harder to read, and makes it harder to keep your data model abstract.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources