FULL JOIN Not working as required

Database

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

Tags (1)
7 REPLIES
Teradata Employee

Re: FULL JOIN Not working as required

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

Re: FULL JOIN Not working as required

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)

Supporter

Re: FULL JOIN Not working as required

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.

Re: FULL JOIN Not working as required

Sorry its just :

Select ID, 

Date, 

Count(Col4) as D, 

Count(Col5) as E, 

Count(Col6) as F

FROM T1

Group By ID, Date

Supporter

Re: FULL JOIN Not working as required

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!

Supporter

Re: FULL JOIN Not working as required

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. 

Senior Apprentice

Re: FULL JOIN Not working as required

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