FULL JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

FULL JOIN

Author

Topic

syedasiftanveer
Starting Member

2 Posts

Posted - 2012-05-18 : 01:06:09

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

syedasiftanveer
Starting Member

2 Posts

Posted - 2012-05-18 : 01:07:29

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-19 : 14:42:31

[code]
SELECT ID,
[DATE],
MAX([A]) AS [A],
MAX([B]) AS [B],
MAX([C]) AS [C],
MAX([D]) AS [D],
MAX([E]) AS [E],
MAX([F]) AS [F]
(
SELECT ID, [DATE], A, B, C,
CAST(NULL AS int) AS [D],
CAST(NULL AS int) AS [E],
CAST(NULL AS int) AS [F]
FROM Q1

UNION ALL

SELECT
ID, [DATE],
NULL,
NULL,
NULL,
D,
E,
F
FROM Q2
)t
GROUP BY ID,[DATE]
[/code]

Q1 and Q2 are two queries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources