Querry two tables for differances
-
Hello,
I have two table that contain the same primary keys. Sometimes on table is missing data. How can I execute a querry that returns only the record from either table that does not exist in the other table?
Thanks,
-
something like this:
declare @Foo table (
dbkey int,
dbdata varchar(10))
declare @foo2 table (
dbkey int,
dbdata varchar(10))
insert into @Foo values (1,'one')
insert into @Foo values (2,'two')
insert into @Foo values (5,'five')
insert into @foo2 values (1,'one')
insert into @foo2 values (2,'two')
insert into @foo2 values (3,'three')
select 'In @Foo not in @foo2' record_state,
f.dbkey,
f.dbdata
from @Foo f
left outer join @foo2 f2
on f.dbkey = f2.dbkey
where f2.dbkey is null
union
select 'In @foo2 not in @Foo' record_state,
f2.dbkey,
f2.dbdata
from @foo2 f2
left outer join @Foo f
on f2.dbkey = f.dbkey
where f.dbkey is null
-
Or use a full outer join
select case when f.dbkey is null then 'In @foo' else 'In @foo2' end as record_state, f.dbkey as f_dbkey, f2.dbkey as f2_dbkey, f.dbdata as f_dbdata, f2.dbdata as f2_dbdata from @foo f full outer join @foo2 f2 on f.dbkey = f2.dbkey where (f2.dbkey is null) or (f1.dbkey is null)
It all depends on how you want the data - the union might give nicer results since it effectively treats both tables equally (first query is A not B, then next is B not A) - the full outer join doesn't mask this - you get the values from both tables in a single row (although this may suit you better)...
Good to have options in any case
-
Thanks, both of those I will find useful. Great Job.
I am curious to Ian's response. I tried yours too, and don't under the results,
here is what I came up with
use rsms
declare @StartDate NVARCHAR(24)
declare @EndDate NVARCHAR(24)
SET @startdate ='11/28/2005 07:00:00'
SET @enddate='11/29/2005 07:00:00'
select
case when B.[START DATE/TIME] is null then 'In B' else 'In A' end as record_state,
B.[START DATE/TIME] AS RSMS_DT, A.[START_DATE_TIME] AS RML2_DT
from [history-roll ground] B
full outer join RML2..RM.RM_RS_HISTORY_ROLL_GROUND A
on B.[START DATE/TIME] = A.[START_DATE_TIME]
where ((A.[START_DATE_TIME] is null) or (B.[START DATE/TIME] is null) AND
(A.start_date_time between @StartDate AND @enddate))
ORDER BY A.[START_DATE_TIME] DESC
and the results were
In B NULL 2005-11-29 06:00:48.000
In B NULL 2005-11-29 04:52:59.000
In B NULL 2005-11-29 04:08:58.000
In B NULL 2005-11-28 17:33:10.000
In B NULL 2005-11-28 16:11:47.000
In A 2005-08-24 03:07:24.000 NULL
In A 2005-08-24 10:59:06.000 NULL
In A 2005-12-01 06:33:10.000 NULL
the records on A are outside of the date range, how did they get there. I tried a few different varations of the where clause, but guess I don't full understand what is going here. Either way, the results from B were correct.
Thanks to both of you for the help.
Brian
-
Because the case statement is wrong. The rows that say "In B" are actually "In A" and vice-versa.
should be
case
when B.[START DATE/TIME] is null then 'In A'
else 'In B'
end as record_state
The results you show in your post actually DO meet the criteria. The record_state column just transposed the text.
-
Whoops!
Thanks for pointing that out
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply