Welcome to weblogs.com.pk Sign in | Join | Help

TSQL :: Comparing multiple fields

Sometimes you want to select from one list the items that exist (or don’t exist) in the second row; and for the comparison you want to use multiple rows. This is common in scenarios where you want to remove duplicate values. You might wish to write something like this in TSQL (This is just an example; not very practical one; but I guess it will work)

create table Users ( userid int, username nvarchar(50) );
create table GoodUsers ( userid int, username nvarchar(50) );

insert into users values (1, 'khurram');
insert into users values (2, 'fareed');
insert into users values (3, 'zaheer');
insert into users values (4, 'bilal');

insert
into goodusers select userid, username from users where userid % 2 = 0

select userid, username
from users
where (userid, username) not in (select userid, username from users)

But sadly pair comparison is not possible in TSQL, Common M$; even mySQL has it!!

Anyways; the simple thing you can do is create a view having a single field which is hash of the fields that you want to compare. E-g

create view vGoodUsers as select convert(nvarchar(50), userid) + ':' + username rowhash from goodusers

You can then use this view for comparison and get the data that you need. Something like:

select userid from users where convert(nvarchar(50), userid) + ':' + username not in (select rowhash from vgoodusers)

You should use the new Checksum functions in SQL 2005 instead of implementing your own hashing...

Published Sunday, June 18, 2006 12:54 AM by khurram
Filed under: ,

Comments

# re: TSQL :: Comparing multiple fields

Sunday, June 18, 2006 12:32 PM by aziz
I think this query has a typo mistake.
Original :
select userid, username
from users
where (userid, username) not in (select userid, username from users)

What i think :
select userid, username
from users
where (userid, username) not in (select userid, username from goodUsers)

# re: TSQL :: Comparing multiple fields

Tuesday, June 20, 2006 5:15 PM by Khurram
Yes, Aziz version is the one I meant!
New Comments to this post are disabled