Problem with a join due to multiple Nulls

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



I think a select distcinct statement with a full outer join may do what
I want, but I'm not certain so want to check.

Regards,

Ciar=E1n

chudson...@hotmail.com  wrote:
> 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
>
>
>
> I think a select distcinct statement with a full outer join may do what
> I want, but I'm not certain so want to check.
>
> Regards,
>
> Ciar=E1n

It appears that TableA doesn't have a key. Not clear what the key is in
TableB either. Please post DDL rather than sketches of tables otherwise
we just have to guess.

Based on what you've posted I'd say you need to fix some data model
issues (missing keys) before you attempt your query. Maybe that's what
you are trying to do but it isn't obvious how your requested output
will help you.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

TableA.ID and TableB.ID are the respective keys, but the problem is
that they contain multiple Nulls

Regards,
Ciar=E1n

(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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

chudson007@hotmail.com  wrote:
> TableA.ID and TableB.ID are the respective keys, but the problem is
> that they contain multiple Nulls
>
> Regards,
> Ciar=E1n

If they contain nulls then they aren't keys. Every table should have a
key. Fix the design first.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

I like your logic Erland.
That'll work perfectly thanks.

>> want to join 2 tables by a unique ID field [sic], but the ID field &#
91;sic] also has multiple NULLS which I do not want to ignore and I fear the
y will cause duplication. <<

Unh?  "Unique identifier" is redundant and columns are not anything
like fields.  Looking at your personal narrative, I see the magical,
vague "id" column violating the basic principle of data modeling that
data elements name distinct things and are not magical uiversal vague
creatures.

Please post some DDL with a key in each table.  Without a key, these
things are not tables by definition.

You might also wantto take the time to learn RDBMS, so you so you do
not embarass yourself in future postings.

David,

Unfortunately I'm working with data exttracts as opposed to a well
designed system.
The code below demonstrates what i am trying to do.
In TableA My_ID is is either Null or a unique number.
In TableB My_ID is either Null or a number which may not be unique.

I tried using a full outer join the other day, but after 15 hours it
still had not worked so I think I am doing somethiing wrong and need
help.

In my real data TableA contains just under 2 million records 1.2million
of which My_ID is null and TableB conatins 5million records of which
almost 3 million of which My_ID is null.





CREATE TABLE TableA ( My_ID  nvarchar(4000),Field
1
 nvarchar(4000),Count
erA nvarchar(4000))
GO

INSERT INTO TableA
SELECT
'1', 'Paul','1'
UNION all SELECT
'2', 'John','1'
UNION all SELECT
'3', 'Mark','1'
UNION all SELECT
Null, 'Simon','1'
UNION all SELECT
Null, 'Peter','1'


CREATE TABLE TableB ( My_ID  nvarchar(4000),Field
2
 nvarchar(4000),Count
erB nvarchar(4000))
GO

INSERT INTO TableB
SELECT
'1', '23','1'
UNION all SELECT
'1', '24','1'
UNION all SELECT
'4', '26','1'
UNION all SELECT
Null, '27','1'
UNION all SELECT
Null, '28','1'



SELECT     *
FROM         TableA FULL OUTER JOIN
TableB ON TableA.My_ID =3D TableB.My_ID


Regards,
Ciar=E1n

chudson007@hotmail.com  wrote:
> David,
>
> Unfortunately I'm working with data exttracts as opposed to a well
> designed system.
> The code below demonstrates what i am trying to do.
> In TableA My_ID is is either Null or a unique number.
> In TableB My_ID is either Null or a number which may not be unique.
>
> I tried using a full outer join the other day, but after 15 hours it
> still had not worked so I think I am doing somethiing wrong and need
> help.
>
> In my real data TableA contains just under 2 million records 1.2million
> of which My_ID is null and TableB conatins 5million records of which
> almost 3 million of which My_ID is null.
>

I can see that the system isn't well designed. What I'm suggesting is
that you fix it. I assume what you mean by "data extracts" is that you
are importing some data into a database from an outside source over
which you don't have any control. Can't you create your own tables and
import the data into them? That's what I recommend: convert your source
data into a normalized data model BEFORE you attempt any further
processing. You obviously haven't done that yet and I don't understand
what you are trying to achieve with this FULL JOIN. Why would you want
to return a join consisting of 7 million rows?

If you aren't permitted to implement a better design then maybe you'll
have to live with sub-optimal performance. I can't redesign your tables
for you because I don't know what your data means or what the result
you've asked for means.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Unfortunately I am not able to implement a better design.
Is there any more optimal way to ensure I have all transactions from
each table and a match wherever an inner join exists?

Regards,
Ciar=E1n