All Forums Database
syedasiftanveer 3 posts Joined 02/12
17 May 2012
FULL JOIN Not working as required

 

Hi,

 

I came up with a situation where for instance i have a table with columns ( ID, Date, A, B, C, D, E, F)

 

ID, DATE, A, B, C comes from one query

and

ID, DATE, D, E, F comes from another query

so i was using full outer join for both queries to populate the table... I want all the records with matching ID,DATE as well non matching to be part of result set. like for example

 

Query 1 outputs:

ID, DATE, A, B, C

-------------------------

ID-1, 1-1-2012, 5, 4, 3

ID-2, 1-1-2012, 3, 5, 4

ID-3, 1-1-2012, 9, 5, 4

 

Query 2 outputs:

ID, DATE, D, E, F

-------------------------

ID-1, 1-1-2012, 7, 8, 9

ID-4, 1-1-2012, 9, 5, 4

ID-3, 1-1-2012, 6, 5, 4

 

Desired output is :

 

ID, Date, A, B, C, D, E, F

-------------------------------

ID-1, 1-1-2012, 5, 4, 3, 7, 8, 9

ID-2, 1-1-2012, 3, 5, 4, null, null, null

ID-3, 1-1-2012, 9, 5, 4, 6, 5, 4

ID-4, 1-1-2012, null, null, null, 9, 5, 4

 

 

Any ideas on this

 

Thanks

Regards, Asif
Tags:
ToddAWalter 316 posts Joined 10/11
17 May 2012

please show the query you tried and the results of that query.

syedasiftanveer 3 posts Joined 02/12
17 May 2012

 

The query i tried is like:

 

Select ID, 

Date, 

Count(Col1) as A, 

Count(Col2) as B, 

Count(Col3) as C,

T2.D, 

T2.E, 

T2.F

FROM T1

 

FULL OUTER JOIN

Select ID, 

Date, 

Count(Col4) as D, 

Count(Col5) as E, 

Count(Col6) as F

FROM T1

Group By ID, Date, D, E, F

)T2

 

ON T2.ID=ID AND t2.Date=Date

Group By ID, Date, T2.D, T2.E, T2.F

 

------------------------------------------------------------------------------

The individual SELECT query results are as above in the thread.

 

but after full Join it just bring the records from left table and the matching records (like left outer join)

Regards,
Asif

ulrich 816 posts Joined 09/09
17 May 2012

Are you sure that this is the query you run?

Select ID, 

Date, 

Count(Col4) as D, 

Count(Col5) as E, 

Count(Col6) as F

FROM T1

Group By ID, Date, D, E, F

 

group by d,e,f

doesn't make sense to me and if d,e,f exists in t1 miight give an unexpected result.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

syedasiftanveer 3 posts Joined 02/12
17 May 2012

Sorry its just :

 

Select ID, 

Date, 

Count(Col4) as D, 

Count(Col5) as E, 

Count(Col6) as F

FROM T1

Group By ID, Date

Regards,
Asif

ulrich 816 posts Joined 09/09
18 May 2012

OK, but I still don't get it what you want to do as I can't figure out the question behind this.

 

FROM T1

 

FULL OUTER JOIN

Select ID, 

Date, 

Count(Col4) as D, 

Count(Col5) as E, 

Count(Col6) as F

FROM T1

Group By ID, Date

)T2

 

so, you query T1 directly and T2 is a derived table of T1.

count(col4), count(col5) etc. will give all the same number - as long as you do not specify distinct. So the SQL does not match to the data you have given.

 

You need to be precise if you want help here!

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
18 May 2012

The easiest thing is to set up a pice of test data (inlc. create table, inserts, SQL) and add it to the post. Which can others past to SQLA and can come up with an real solution - instead of guessing and seeing that description does not match. 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
18 May 2012

Maybe you just forgot to use COALESCE(T1.id,T2.id), COALESCE(T1.date,T2.date), otherwise all non-existing rows from the left table will have NULLs and will be aggregated into a single row.

Dieter

Dieter

You must sign in to leave a comment.