Full outer join issue

[FloggingDolphin]

Hi there,

I'm kinda new to this so I hope I've explained it properly. Please ask if you need any additional info. Here is a simplified version of the issue I'm experiencing:

I have two financial tables. One table ("ACTUAL") contains a list of employees and their actual spending amounts for various periods. The other table ("BUDGET") contains a list of employees and their budget spending amounts for various periods. Ie. Both tables have idential columns: empno,amount and period.

I'd like to do a simple query where I will get the Employee name displayed, the total actual amount, and the total budget amount for each employee. Where the budget contains an employee with an amount but the actuals don't I would like to still display it and put a blank or zero in the amount column for "ACTUAL" table.

I would have thought the following code would work:

Code:

 
DBSELECT COALESCE(a.EMPNO,b.empno) AS EMPNO, SUM(NVL(a.amount,0)) AS actamount, SUM(NVL(b.amount,0)) AS budamount
FROM ACTUAL a FULL OUTER JOIN BUDGET b ON a.EMPNO=b.EMPNO 
 

However, all I get is the entries where there was a 100% match on the employee numbers. Ie. my OUTER JOIN does not seem to function.
Also (as you may have noticed), I'm not sure how to only display the SUM total of all the employees rather than totals for individual periods.

Additional info:

Platform : Win 2000
Language : SQL (Agresso SQL, can be forced to Oracle SQL via DBSELECT)

Hope someone can help, thanks

FD

[FloggingDolphin]

.........Anyone?

[MMcCarthy]

Try this ...

SELECT b.empno AS EMPNO, SUM(NVL(a.amount,0)) AS actamount, SUM(NVL(b.amount,0)) AS budamount
FROM ACTUAL a RIGHT JOIN BUDGET b 
ON a.EMPNO=b.EMPNO
GROUP BY  b.empno