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:

SELECT a.EMPNO,b.empno, a.amount AS actamount, b.amount 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, Agresso server uses MSSQL)

Hope someone can help, thanks

FD

[iburyak]

Try this:


[PHP]
SELECT a.EMPNO, sum(isnull(a.am ount,0) AS actamount, sum(isnull(b.am ount,0) AS budamount
FROM ACTUAL a
LEFT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO[/PHP]

[FloggingDolphin]

Try this:


[PHP]
SELECT a.EMPNO, sum(isnull(a.am ount,0) AS actamount, sum(isnull(b.am ount,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

[iburyak]

I assume that EMPNO exists in both tables, if not I would do following:

SELECT a.EMPNO, sum(isnull(a.am ount,0) AS actamount, sum(isnull(b.am ount,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.am ount,0) AS actamount, sum(isnull(b.am ount,0) AS budamount
FROM ACTUAL a
RIGHT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO

Good Luck.

[iburyak]

Also try this:


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

[iburyak]

Above statement is not correct so try this one instead:


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

[FloggingDolphin]

Above statement is not correct so try this one instead:


[PHP]SELECT coalesce(a.EMPN O, b.EMPNO) EMPNO, sum(isnull(a.am ount,0) AS actamount, sum(isnull(b.am ount,0) AS budamount
FROM ACTUAL a
FULL OUTER JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY coalesce(a.EMPN O, 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

[iburyak]

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.

[FloggingDolphin]

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=bu dget table,agltransa ct=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.:

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
FROM abutrans b FULL OUTER JOIN agltransact t
ON t.dim_3=b.dim_3 AND t.dim_1=b.dim_1
WHERE t.client='CC' AND b.client='CC' AND t.account='1150' AND b.account='1150' AND t.period='200705' AND b.period='200705'
AND COALESCE(b.dim_1,t.dim_1)='12345'
GROUP BY COALESCE(b.dim_3,t.dim_3),COALESCE(b.dim_1,t.dim_1)

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

[FloggingDolphin]

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

[iburyak]

Try this:

[PHP]Select empno,COSTCENT, Sum(isnull(acta mt,0) actamt, sum(isnull(buda mt,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?

[iburyak]

Correction to above query:

Select empno,COSTCENT, Sum(isnull(acta mt,0)) actamt, sum(isnull(buda mt,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

[iburyak]

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.EMPN O, b.EMPNO) EMPNO, sum(isnull(a.am ount,0)) AS actamount, sum(isnull(b.am ount,0)) AS budamount
FROM ACTUAL a
FULL OUTER JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY coalesce(a.EMPN O, b.EMPNO) [/PHP]

[FloggingDolphin]

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

[iburyak]

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… :)