Posted by chudson007 on 03/31/06 13:28

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),Field1
nvarchar(4000),CounterA 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),Field2
nvarchar(4000),CounterB 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 = TableB.My_ID


Regards,
Ciarán

• Next in forum: JDBC Driver NOT throwing Exception
• Prev in forum: error when trying to do datamining
• Thread view: Re: Problem with a join due to multiple Nulls

[Reply to this message]