Removing duplicates from two tables

[ng38]

Hi,

I have two tables where both contain the ID number and Date. The first table will always have a date and the second table will never have a date. Basically I want the query to bring back a result set that if the ID is in table 1, then don't grab the row with the same ID from table 2. I've tried unioning and right joining, but that didn't seem to work. Below is an example of what I'm looking for.

Table 1

ID | Date
---------------

1 Dec 20
4 Dec 20

Table 2

ID | Date
---------------
1 NULL
2 NULL
3 NULL
4 NULL

Results Table

ID | Date
---------------
1 Dec 20
2 NULL
3 NULL
4 Dec 20

Does anyone have any clues on how I'd go about doing this?

Thanks.

[CrappyCoder]

Have you tried the following?...

SELECT T2.ID, T1.Date
FROM T2
LEFT JOIN T1
ON T2.ID = T1.ID

You will get everyting in table 2 (which has an ID but no date) and only the date from table 1 (which has an ID and a date)

The you can filter on the result as you see fit

[fx1250s]

select A.id, A.aDate
from table1 A
union
select B.id, B.aDate
from table2 B
where B.id not in (select A.id from table1 A)

-- Complete test code:

drop table table1
drop table table2
go

create table table1
(
id int PRIMARY KEY
, adate datetime
)

go

create table table2
(
id int PRIMARY KEY
, adate datetime
)

go

insert table1 (id, adate)
select 1, '2010/01/01'
union
select 2, '2010/01/01'
union
select 5, '2010/01/02'
union
select 6, '2010/01/03'

insert table2 (id)
select 1
union
select 2
union
select 3
union
select 4

go

select A.id, A.adate
from table1 A
union
select B.id, B.adate
from table2 B
where B.id not in (select A.id from table1 A)

[eralper]

Hello all,
With SQL2008 a new command MERGE is introduced for use of developers.

For this sample this command fits very well.

here is my solution.

select * from Table1
select * from Table2

MERGE Table1
USING
  (
    SELECT Id, [Date] FROM Table2
  ) MergeData ON Table1.id = MergeData.id
  WHEN NOT MATCHED BY TARGET THEN 
    INSERT VALUES (id, [Date])
  ;
GO

select * from Table1
select * from Table2

Please refer for more on T-SQL MERGE command enhancement in SQL Server 2008 and T-SQL MERGE Statement Example

I hope that helps