>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