469,138 Members | 1,383 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT a.EMPNO,b.empno, a.amount AS actamount, b.amount 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, Agresso server uses MSSQL)

Hope someone can help, thanks

FD
Mar 26 '07 #1
20 4947
iburyak
1,017 Expert 512MB
Try this:


[PHP]
SELECT a.EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
LEFT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO[/PHP]
Mar 26 '07 #2
Try this:


[PHP]
SELECT a.EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
LEFT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO[/PHP]
Thanks a lot; I'll be sure to try that at work tomorrow.
One question, since it is a left join, what if the other table contains data that the ACTUAL does not?

thanks in advance,
FD
Mar 27 '07 #3
iburyak
1,017 Expert 512MB
I assume that EMPNO exists in both tables, if not I would do following:

SELECT a.EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
LEFT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO
UNION
SELECT b.EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
RIGHT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO
Good Luck.
Mar 27 '07 #4
iburyak
1,017 Expert 512MB
Also try this:


[PHP]SELECT coalesce(a.EMPNO, b.EMPNO) sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
FULL OUTER JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO [/PHP]
Mar 27 '07 #5
iburyak
1,017 Expert 512MB
Above statement is not correct so try this one instead:


[PHP]SELECT coalesce(a.EMPNO, b.EMPNO) EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
FULL OUTER JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY coalesce(a.EMPNO, b.EMPNO) [/PHP]
Mar 27 '07 #6
Above statement is not correct so try this one instead:


[PHP]SELECT coalesce(a.EMPNO, b.EMPNO) EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
FULL OUTER JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY coalesce(a.EMPNO, b.EMPNO) [/PHP]
Hi again,

Thanks so much for the further assistance.
I've finally got to try them all. Weird thing really...at first it totally didn't work and said that whole thing is incorrect. So I just tried to incorporate the COALESCE into the existing queries I tried and have had some success. Still working on it.

One little thing I'm concerned is that maybe it will display the employee numbers correctly but the amounts wrongly. If that were to happen, I could just do a query to show the employee numbers and then V-Lookup the amounts on two separate queries on Actual and Budget.

I'll keep posted on my progress and post the whole code once it works.

Thanks so very much for your help!
FD
Apr 4 '07 #7
iburyak
1,017 Expert 512MB
I am not sure why this query doesn't work.
You didn't post any error messages.
Numbers should be correct and coalesce will work only in cases when you have entree in one table and not in both. This way you will always see EMPNO even if it is missing from one of the tables.

Group by makes sure that count is for specific EMPNO only.

To make sure, do some testing first.
Apr 4 '07 #8
I am not sure why this query doesn't work.
You didn't post any error messages.
Numbers should be correct and coalesce will work only in cases when you have entree in one table and not in both. This way you will always see EMPNO even if it is missing from one of the tables.

Group by makes sure that count is for specific EMPNO only.

To make sure, do some testing first.
Hi there,

I've managed to get it to sort of work. Now I get the error : "Missing right parenthesis". I'm not sure why. I've done another table though that does work, however it gives me two problems.

1. Amounts are incorrect whether I use SELECT DISTINCT or not.
2. Only shows employees from the Actual table.

Here is my code; please note dim_3 is the empno column and dim_1 is a cost centre column we use,abutrans=budget table,agltransact=actual table, client=our company,period=financial period of transactions.
I just left it all in case you need it,but I guess you don't need any of the WHERE bit to fully understand.:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT COALESCE(b.dim_3,t.dim_3) AS empno,COALESCE(b.dim_1,t.dim_1) AS COSTCENT ,SUM (t.amount) AS actamt, sum(b.amount) AS budamt
  3. FROM abutrans b FULL OUTER JOIN agltransact t
  4. ON t.dim_3=b.dim_3 AND t.dim_1=b.dim_1
  5. WHERE t.client='CC' AND b.client='CC' AND t.account='1150' AND b.account='1150' AND t.period='200705' AND b.period='200705'
  6. AND COALESCE(b.dim_1,t.dim_1)='12345'
  7. GROUP BY COALESCE(b.dim_3,t.dim_3),COALESCE(b.dim_1,t.dim_1)
  8.  
  9.  
If you need me to send a simplified version like before. Just let me know, thanks.

If I can just get it to display all the employee numbers in both tables, that would solve 80% of my problems (I can circumvent the incorrect amounts by doing a v-lookup).


thanks in advance,
FD
Apr 7 '07 #9
I've further tested it and it doesn't seem to recognise the ISNULL function. It also doesn't seem to like if I do the SUM with the ,0 bit.

It looks like it only displayed employee numbers where they both exist in the tables.

FD
Apr 7 '07 #10
iburyak
1,017 Expert 512MB
Try this:

[PHP]Select empno,COSTCENT, Sum(isnull(actamt,0) actamt, sum(isnull(budamt,0)) budamt

FROM(
SELECT COALESCE(b.dim_3,t.dim_3) AS empno,
COALESCE(b.dim_1,t.dim_1) AS COSTCENT ,
t.amount AS actamt,
b.amount AS budamt
FROM abutrans b FULL OUTER JOIN agltransact t
ON t.dim_3=b.dim_3 AND t.dim_1=b.dim_1
) TMP

GROUP BY empno,COSTCENT[/PHP]


Isnull must work.
To test it run following statement:


[PHP]select isnull(null, 0) NULL_VALUE, isnull(123,0) NOT_NULL_VALUE[/PHP]

If you use sum() and you have null values it will not do correct additions.

Try following statement to check my words:


[PHP]select null + 5 Sum_NULL, isnull(null, 0) + 5 Sum_With_Isnull[/PHP]

Use where condition later it prevents you from returning correct records.

You mentioned something about bit datatype. What about it? Which column is a bit?
Apr 8 '07 #11
iburyak
1,017 Expert 512MB
Correction to above query:

Select empno,COSTCENT, Sum(isnull(actamt,0)) actamt, sum(isnull(budamt,0)) budamt

FROM(
SELECT COALESCE(b.dim_3,t.dim_3) AS empno,
COALESCE(b.dim_1,t.dim_1) AS COSTCENT ,
t.amount AS actamt,
b.amount AS budamt
FROM abutrans b FULL OUTER JOIN agltransact t
ON t.dim_3=b.dim_3 AND t.dim_1=b.dim_1
) TMP

GROUP BY empno,COSTCENT
Apr 8 '07 #12
iburyak
1,017 Expert 512MB
Hi again,

Thanks so much for the further assistance.
I've finally got to try them all. Weird thing really...at first it totally didn't work and said that whole thing is incorrect. So I just tried to incorporate the COALESCE into the existing queries I tried and have had some success. Still working on it.

One little thing I'm concerned is that maybe it will display the employee numbers correctly but the amounts wrongly. If that were to happen, I could just do a query to show the employee numbers and then V-Lookup the amounts on two separate queries on Actual and Budget.

I'll keep posted on my progress and post the whole code once it works.

Thanks so very much for your help!
FD
I missed two parentheses here I didn't test it that's why couldn't catch it:
I corrected it. You might want to try it too.


[PHP]SELECT coalesce(a.EMPNO, b.EMPNO) EMPNO, sum(isnull(a.amount,0)) AS actamount, sum(isnull(b.amount,0)) AS budamount
FROM ACTUAL a
FULL OUTER JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY coalesce(a.EMPNO, b.EMPNO) [/PHP]
Apr 8 '07 #13
Hi

Thanks a lot for all that help. We're having a long Easter weekend here, but as soon as I get to work I'll be sure to try it.

About the "bit" part. There is no bit column or bit datatype. Sorry, I meant "bit" in the context of a small piece/part or quantity. You can ignore that bit completely. =)

I see your point about the isnull, because at the moment wherever the amounts dont match it repeatedly adds them.

Anyway, if all else fails I'll try get my IT department to install MS Access and learn that.

thanks again and have a blessed Easter~

FD
Apr 9 '07 #14
iburyak
1,017 Expert 512MB
MS Access is part of Microsoft Office and is probably installed on your computer. But, beside the point, I am amazed how many professional people resort to half way database like Access instead of working with real thing like SQL.

Access designed for very small businesses or students. Not sure how big it became in professional world. I assume it is European thing. I never heard of this wide use like on this site really… :)
Apr 9 '07 #15
MS Access is part of Microsoft Office and is probably installed on your computer. But, beside the point, I am amazed how many professional people resort to half way database like Access instead of working with real thing like SQL.

Access designed for very small businesses or students. Not sure how big it became in professional world. I assume it is European thing. I never heard of this wide use like on this site really… :)
hahaha, well we use MS Access sometimes to do quick and dirty queries. Problem is...I just read that Access doesn't have FULL OUTER JOINS in it's GUI. However, apparently there is still a way to get it with some extra SQL.

Anyway, I've been trying that ISNULL test. At first it side invalid identifier "ISNULL", so I tried IS NULL (with a space between) and then it kept saying about a missing expression. Sigh, I have a feeling it doesn't like ISNULL.

I can't try that test you gave me on its own because Agresso excellerator always requests that you specificy at least two columns ie. after the query we actually add the lines "query","columns (followed by the columns to display in each excel cell that were chosen in the SELECT statement) and then "query" or "group (column)" depending on your preference.

will keep trying...
Mark
Apr 9 '07 #16
iburyak
1,017 Expert 512MB
hahaha, well we use MS Access sometimes to do quick and dirty queries. Problem is...I just read that Access doesn't have FULL OUTER JOINS in it's GUI. However, apparently there is still a way to get it with some extra SQL.

Anyway, I've been trying that ISNULL test. At first it side invalid identifier "ISNULL", so I tried IS NULL (with a space between) and then it kept saying about a missing expression. Sigh, I have a feeling it doesn't like ISNULL.

I can't try that test you gave me on its own because Agresso excellerator always requests that you specificy at least two columns ie. after the query we actually add the lines "query","columns (followed by the columns to display in each excel cell that were chosen in the SELECT statement) and then "query" or "group (column)" depending on your preference.

will keep trying...
Mark
Did you try to execute my example in Access?
[PHP]
select null + 5 Sum_NULL, isnull(null, 0) + 5 Sum_With_Isnull [/PHP]


To have full join in access you should do following:

[PHP]select ...
from table1 left join table2 on .....
UNION
Select .....
from table1 right join table2[/PHP]

Good Luck.
Apr 9 '07 #17
Did you try to execute my example in Access?
[PHP]
select null + 5 Sum_NULL, isnull(null, 0) + 5 Sum_With_Isnull [/PHP]


To have full join in access you should do following:

[PHP]select ...
from table1 left join table2 on .....
UNION
Select .....
from table1 right join table2[/PHP]

Good Luck.
Hi

Thanks for the fast reply. I couldn't test Access yet as it seems it's not installed on my PC. Due to some irritating red tape and licensing I have to wait for IT to get it for me.

I'll be sure to keep trying a way to get ISNULL to work. There must be a way for it to show two values from different tables side by side where sometimes one table has an employee but the other one does not.

kind regards,
FD
Apr 9 '07 #18
iburyak
1,017 Expert 512MB
I never saw such behavior.

Can you give me sample data and exact query you execute and result you get?
I don't think it should be an issue as you described.

I assume you do have access to SQL server.
Apr 9 '07 #19
I never saw such behavior.

Can you give me sample data and exact query you execute and result you get?
I don't think it should be an issue as you described.

I assume you do have access to SQL server.
Hi,

Sure I can send you the data (with modified figures to prevent breach of confidentiality). How would you like to receive them? In an Excel sheet or...?

Anyway, if all else fails, how would I do a query to show me all the employee numbers in the transactions of both tables (ie. in order smallest to biggest) without repeating themselves?

thanks again,
FD
Apr 10 '07 #20
iburyak
1,017 Expert 512MB
Send me a Private Message.
Or just show me couple of numbers you have, query you execute, result you get and desired result.
Apr 10 '07 #21

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
1 post views Thread by Martijn van Oosterhout | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.