473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 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:

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 5211
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

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

Similar topics

1
by: Dave | last post by:
Hi I have the following 4 tables and I need to do a fully outerjoin on them. create table A (a number, b number, c char(10), primary key (a,b)) create table B (a number, b number, c ...
0
by: reneecccwest | last post by:
SELECT d.code, d.description, v.code AS divCode, v.descripton AS divDescript, b.code AS brhCode, b.description AS brhDescript FROM Department d FULL OUTER JOIN Division v
7
by: alexcn | last post by:
I have the following query: SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode, dbo.tSymMain.smCode FROM dbo.tSymExch FULL OUTER JOIN dbo.tSymGrp ON dbo.tSymExch.exID =...
2
by: Lee | last post by:
I have two identical schema tables (one in a production library and another in a development library) and I need to be able to easily compare the data in them for errors and omissions. With...
2
by: Cory | last post by:
i have two tables, Event1 and Event2. They both have fields called contactID and eventID in them. Some of the contactID's between the tables are equal and some are not. I want to do a full outer...
1
by: Martijn van Oosterhout | last post by:
Today I got the error: ERROR: FULL JOIN is only supported with mergejoinable join conditions Which is really annoying since a full join is exactly what I wanted. I guess the alternative is to...
2
by: FloggingDolphin | last post by:
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...
1
by: shilpasharma | last post by:
Hi, Can anybody let me know how I can optimise following Query. Select * from reports where ( exists ( SELECT 1 FROM results_required rr, item_claims_trials ict, results res WHERE...
0
by: brunodamato | last post by:
In the example that follows, I am receiving an incorrect result set in the View. I am looking to get the FULL result set from View_LY and Table_TY. Instead, this View is returning the Common (Equi)...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

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.