Hi, I have 2 identically defined tables that should have duplicate rows
(majority). I want a way of displaying just the data that doesn't exit in
either table, one table at a time for reporting purposes.
I have coded this already using a 3rd table that holds all data that matches
2 tables and then deleting from both tables the data that matches the third
table and then doing a select from the result in each table.
I want to know how to do this more efficiently as this way seems clumsy and
slow. Can anyone help?For non-nullable columns:
SELECT A.*
FROM A
LEFT JOIN B
ON A.col1 = B.col1
AND A.col2 = B.col2
AND ... etc
WHERE B.col1 IS NULL
If you need to cope with NULLs by treating them as equal values in the
comparison:
SELECT col1, col2, ...
FROM
(SELECT 1 AS x, col1, col2, ...
FROM A
UNION ALL
SELECT 2 AS x, col1, col2, ...
FROM B) AS T
GROUP BY col1, col2, ...
HAVING MAX(x)=1
David Portas
SQL Server MVP
--|||select * from table1 where ID not in (select ID from table2)
union
& vice versa
Does this help?
Daniel
"sysbox27" <sysbox27@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E691E4AE-E0B6-4120-A072-B42001AE47EB@.microsoft.com...
> Hi, I have 2 identically defined tables that should have duplicate rows
> (majority). I want a way of displaying just the data that doesn't exit in
> either table, one table at a time for reporting purposes.
> I have coded this already using a 3rd table that holds all data that
> matches
> 2 tables and then deleting from both tables the data that matches the
> third
> table and then doing a select from the result in each table.
> I want to know how to do this more efficiently as this way seems clumsy
> and
> slow. Can anyone help?
>|||Allow me to illustrate:
Let's compare these two tables:
create table dbo.Names1
(
NameID int identity (1, 1)
,[Name] nvarchar(64) primary key
)
go
create table dbo.Names2
(
NameID int identity (1, 1)
,[Name] nvarchar(64) primary key
)
go
insert dbo.Names1
(
[Name]
)
select N'Jack' as [Name]
union
select N'Phil'
union
select N'Rod'
union
select N'Bing'
go
insert dbo.Names2
(
[Name]
)
select N'Jack' as [Name]
union
select N'Tommy'
union
select N'Midge'
union
select N'Bing'
go
Like this:
select Combination.[Description] as [Description]
,Combination.[Name] as [Name]
from (
select 'Exists in Names1' as [Description]
,dbo.Names1.[Name] as [Name]
from dbo.Names1
full join dbo.Names2
on dbo.Names2.[Name] = dbo.Names1.[Name]
where (dbo.Names1.NameID is null or dbo.Names2.NameID is null)
union
select 'Exists in Names2'
,dbo.Names2.[Name]
from dbo.Names1
full join dbo.Names2
on dbo.Names2.[Name] = dbo.Names1.[Name]
where (dbo.Names1.NameID is null or dbo.Names2.NameID is null)
) Combination
where (Combination.[Name] is not null)
go
Is this what you're looking for?
ML|||thank you to everyone for taking the time to assist me.
much appreciated.
