469,273 Members | 1,705 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,273 developers. It's quick & easy.

Full outer join issue

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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. DBSELECT COALESCE(a.EMPNO,b.empno) AS EMPNO, SUM(NVL(a.amount,0)) AS actamount, SUM(NVL(b.amount,0)) AS budamount
  3. FROM ACTUAL a FULL OUTER JOIN BUDGET b ON a.EMPNO=b.EMPNO 
  4.  
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
Apr 11 '07 #1
2 1586
.........Anyone?
Apr 16 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT b.empno AS EMPNO, SUM(NVL(a.amount,0)) AS actamount, SUM(NVL(b.amount,0)) AS budamount
  2. FROM ACTUAL a RIGHT JOIN BUDGET b 
  3. ON a.EMPNO=b.EMPNO
  4. GROUP BY  b.empno
  5.  
Apr 17 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Dave | last post: by
reply views Thread by reneecccwest | last post: by
7 posts views Thread by alexcn | last post: by
2 posts views Thread by Lee | last post: by
5 posts views Thread by Dev | last post: by
1 post views Thread by Martijn van Oosterhout | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.