Help with Where Not Exists / Avoiding Loops

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Where Not Exists / Avoiding Loops

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2008-03-18 : 07:41:15

I am trying to figure out an efficient way of comparing two tables of identical structure and primary keys only I want to do a join where one of the tables reveals values for records which have been modified and/or updated.

To illustrate, I have two tables in the generic form:

id-dt-val

For which the 'val' in table 2 could be different from the 'val' in table 1 - for a given id-dt coupling that are identical in both tables.

Does anyone know of an efficient way I could return all id-dt couplings in table 2 which have values that are different from those with the same id-dt couplings in table 1?

NOTE: I am asking this because I am trying to avoid explicit comparisons between the 'val' columns. The tables I am working with in actuality have roughly 900 or so columns, so I don't want this kind of a monster query to do (otherwise, I would simply do something like where a.id = b.id and a.dt = b.dt and a.val <> b.val) - but this won't do in this case.

As a sample query, I have the following script below. When I attempt the where not exists, as you might expect, I only get the one record in which the id-dt coupling is different from those in table 1, but I'm not sure how to return the other records where the id-dt coupling is the same in table 1 but for where modified values exist:

create table #tab1
(
id varchar(3),
dt datetime,
val float
)
go

create table #tab2
(
id varchar(3),
dt datetime,
val float
)
go

insert into #tab1
values
('ABC','01/31/1990',5.436)
go
insert into #tab1
values
('DEF','01/31/1990',4.427)
go
insert into #tab1
values
('GHI','01/31/1990',7.724)
go

insert into #tab2
values
('XYZ','01/31/1990',3.333)
go
insert into #tab2
values
('DEF','01/31/1990',11.111)
go
insert into #tab2
values
('GHI','01/31/1990',12.112)
go

select a.* from #tab2 a --Trouble is, this only returns the XYZ record
where not exists
(select b.* from #tab1 b where a.id = b.id and a.dt = b.dt)
go

drop table #tab1
drop table #tab2
go

I really dont' want to have to code up a loop to do the value by value comparison for inequality, so if anyone knows of an efficient set-based way of doing this, I would really appreciate it.

Any advice appreciated!

-KS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 07:48:12

Is there any special reason why you dont want to use join to achieve this?

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2008-03-18 : 08:13:47

Hi,

No, not really - unless if it means that I have to write out all 900 columns by hand (that's essentially what I'm trying to avoid).

In other words, I can do this:

select a.* from #tab2 a
inner join #tab1 b
on (a.id = b.id and a.dt = b.dt and a.val <> b.val)

but I don't want to have to write out the 900 'val' columns I have in my real tables. Is there a better join that you know of which would obviate the need to write all such columns out?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-18 : 08:23:54

at what point in development are you? ...too late to come up with a different solution?

sounds like you're trying to implement a form of 'change data capture', which is actually built in to SQL2008.

otherwise, if you want to identify what has changed without joining to an 'original' / or base table, you could do it with an auditing type strategy. stick a datetime column on that updates every time something changes? etc...

Em

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-18 : 08:25:49

SELECT c.ID,c.dt,count(*)
FROM
(select * from tableA
union all
select * from tableB)c
GROUP BY c.ID,c.dt
HAVING COUNT(*) = 1

Will give you a list of all records in tableA and TableB that don't match on at least one of the columns.

JimF

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-18 : 08:28:33

HAVING COUNT(*) > 1 !

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2008-03-18 : 08:30:12

Wow!!!

Thanks, JimF - that gave me exactly what I needed!

I really appreciate your help - hope I'll be able to reciprocate some day, though somehow I doubt I could shed light on anything for you.

-KS

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-18 : 08:45:18

Stick around,Kid, you'd be amazed at what you can learn and suprprised at what you can teach.

Jim
"Every day I learn somtheing that somebody else already knew"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources