471,888 Members | 1,963 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

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 1624
.........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
reply views Thread by YellowAndGreen | last post: by

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.