Re: Problem with a join due to multiple Nulls

chudson...@xxxxxxxxxxx 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án

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

--

.

Relevant Pages

  • Re: Require Solution for this SQL problem
    ... runs every morning and based on some logic dumps rows from TableA to ... Now when I run stored proc I want rows copied as below ... FROM TableB (UPDLOCK) ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Problem with a join due to multiple Nulls
    ... multiple NULLS which I do not want to ignore and I fear they will cause ... Using TableA and TableB below i will demonstrate the problem. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Copy records from one table to another
    ... SQL Server MVP ... Columnist, SQL Server Professional ... I have two identical tables (TableA has data, TableB is empty) ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL help
    ... SQL Server 2005/2008 then EXCEPT: ... SELECT column FROM TableA ... SELECT column FROM TableB; ... Plamen Ratchev ...
    (microsoft.public.sqlserver.programming)
  • Re: Cursors (again)
    ... What does it matter if TableB is temporary? ... or tableA. ... open cTableB ...
    (microsoft.public.sqlserver.programming)