Lost In Joins... 1

[dBjason]

For simplicity, I have two tables. One looks like this:

Employid CUD
101 1000
103 1000
107 4000

...And the other looks like this:

Employid EMM
100 9455
101 9355
102 9455


What I need to do is create a query to do this:

Employid CUD EMM
100 NULL 9455
101 1000 9355
102 NULL 9455
103 1000 NULL
107 4000 NULL


...I've been playing with LEFT, RIGHT, and OUTER Joins all day, only to get NULL or missing values in the employid column, which shouldn't happen. It's really not as simple as two tables, the real query is listed below (the two tables I mentioned earlier are really subqueries which run great on thier own). But as far as I'm concerned, I should be able to treat them as two simple tables and get my desired results. What am I doing wrong?




SELECT CUD.EMPLOYID, CUD.CATCH_UP_DEFERRAL AS CATCH_UP_DEFERRAL_YTD,
EMM.EMPLOYER_MATCH_AMOUNT AS EMPLOYER_MATCH_AMOUNT_YTD

FROM

(SELECT EMPLOYID, SUM(UPRTRXAM) AS CATCH_UP_DEFERRAL FROM UPR30300
WHERE (PYRLRTYP = '3' AND PAYROLCD = 'PSF')
OR
(PYRLRTYP = '2' AND PAYROLCD = 'PSF')
AND YEAR(CHEKDATE) = year(getdate())
GROUP BY EMPLOYID) AS CUD

LEFT JOIN

(SELECT EMPLOYID, SUM(UPRTRXAM) AS EMPLOYER_MATCH_AMOUNT FROM UPR30300
WHERE (PYRLRTYP = '3' AND PAYROLCD = 'PSP')
OR
(PYRLRTYP = '2' AND PAYROLCD = 'PSC')
AND YEAR(CHEKDATE) = YEAR(GETDATE())
GROUP BY EMPLOYID) AS EMM

ON CUD.EMPLOYID = EMM.EMPLOYID

 

[SonOfEmidec1100]

Heres one way I think should work

SELECT     ISNULL(Table1.EmployID, Table2.EmployID) AS EmployID, Table1.CUD, Table2.EMM
FROM         Table1 FULL OUTER JOIN
                      Table2 ON Table1.EmployID = Table2.EmployID
ORDER BY Table1.EmployID