Posted by Erland Sommarskog on 03/29/06 13:25

(chudson007@hotmail.com) writes:
> I want to join 2 tables by a unique ID field, but the ID field also has
> multiple NULLS which I do not want to ignore and I fear they will cause
> duplication.
>
> Using TableA and TableB below i will demonstrate the problem.
>
> TableA
> TableA.ID Field1 Field2
> 1 Paul 1
> Null John 1
> 2 John 1
>
>
> TableB
> TableB.ID Field3 Field4
> 1 25 1
> Null 32 1
> Null 23 1
> 2 26 1
>
> The Table I want is
>
> TableA.ID TableB.ID Field1 Field2 Field3 Field4
> 1 1 Paul 1 25 1
> 2 2 John 1 26 1
> Null Null John 1 Null Null
> Null Null Null Null 32 1
> Null Null Null Null 26 1

The IDs cannot really be keys if there are NULL values, even less if there
are multiple NULL.

If I'm taking a guess of what you are looking for, this might be it:

SELECT a.ID, b.ID, a.Field1, a.Field2, b.Field3, b.Field4
FROM TableA a
JOIN TableB b ON a.ID = b.ID
UNION ALL
SELECT NULL, NULL, a.Field1, a.Field2, NULL, NULL
FROM TableA a
WHERE a.ID IS NULL
UNION ALL
SELECT NULL, NULL, NULL, NULL, b.Field3, b.Field4
FROM TableB b
WHERE b.ID IS NULL


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]