Full join?

giovedì 06 settembre 2007 - 15.08

aleplgr Profilo | Junior Member

Hi! Excuses for writing in english but maybe someone can help me: I'm joining Table1 and Table2 on NUMPAT and TRACT, and I need as a resultset a NUMPAT column showing all NUMPATs and the other columns showing null for those that do not join to anyone:


Table1
NUMPAT TRACT ITEMa
1 100 50
2 134 60
5 200 30

Table2
NUMPAT TRACT ITEMb
1 100 55
3 126 40
4 140 70
5 100 20


Result set
NUMPAT TRACT1 TRACT2 ITEMa ITEMb
1 100 100 50 55
2 134 null 60 null
3 null 126 null 40
4 null 140 null 70
5 200 null 30 null
5 null 100 null 20

lbenaglia Profilo | Guru

>Result set
>NUMPAT TRACT1 TRACT2 ITEMa ITEMb
>1 100 100 50 55
>2 134 null 60 null
>3 null 126 null 40
>4 null 140 null 70
>5 200 null 30 null
>5 null 100 null 20

Hi Alex,

look at this code:

USE tempdb; CREATE TABLE dbo.Table1( NUMPAT int NOT NULL, TRACT int NOT NULL, ITEMa int NOT NULL ); CREATE TABLE dbo.Table2( NUMPAT int NOT NULL, TRACT int NOT NULL, ITEMb int NOT NULL ); INSERT dbo.Table1 VALUES(1, 100, 50); INSERT dbo.Table1 VALUES(2, 134, 60); INSERT dbo.Table1 VALUES(5, 200, 30); INSERT dbo.Table2 VALUES(1, 100, 55); INSERT dbo.Table2 VALUES(3, 126, 40); INSERT dbo.Table2 VALUES(4, 140, 70); INSERT dbo.Table2 VALUES(5, 100, 20); SELECT COALESCE(T1.NUMPAT, T2.NUMPAT) AS NUMPAT , T1.TRACT AS TRACT1 , T2.TRACT AS TRACT2 , T1.ITEMa , T2.ITEMb FROM dbo.Table1 AS T1 FULL JOIN dbo.Table2 AS T2 ON T1.NUMPAT = T2.NUMPAT AND T1.TRACT = T2.TRACT ORDER BY NUMPAT; /* Output: NUMPAT TRACT1 TRACT2 ITEMa ITEMb ----------- ----------- ----------- ----------- ----------- 1 100 100 50 55 2 134 NULL 60 NULL 3 NULL 126 NULL 40 4 NULL 140 NULL 70 5 NULL 100 NULL 20 5 200 NULL 30 NULL (6 row(s) affected) */ DROP TABLE dbo.Table1, dbo.Table2;

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

aleplgr Profilo | Junior Member

THANKS!!!!!! GREAT
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2024
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5