October 2, 2005 at 2:27 am
hI all,
I have a table A with fname, mname,lname , radd,odd etc.
Similarily i have table B with same fields fname, mname,lname , radd,odd
Now i want to return a record where f_name or m_name or lname
matches with either f_name or m_name or lname AND r_add or o_add matches
r_add or o_add of table B.
Its LIKE THIS :
(A.FNAME = B.FNAME OR A.FNAME = B.MNAME OR A.FNAME = B.LNAME OR
A.MNAME= B.FNAME OR A.MNAME= B.MNAME OR A.MNAME= B.LNAME OR
A.LNAME= B.FNAME OR A.LNAME= B.MNAME OR A.LNAME= B.LNAME)
AND
(A.R_ADD = B.RADD OR A.R_ADD = B.ADD OR
A.O_ADD= B.RADD OR A.O_ADD = B.ADD)
Is there a more efficient way to write this select statement ..taking perofrmance into account.
Regards,
Rajesh
October 3, 2005 at 12:04 am
select a.* from a join b
on a.fname in (b.fname,b.mname,b.lname) or
a.mname in (b.fname,b.mname,b.lname) ....
smth like this (i suppose)
October 3, 2005 at 2:44 am
You could try this one, though I'm not sure how efficient it is - you have to test it. It works on the assumption that you have some ID on both tables you are comparing. Query just shows you ID's of the suspicious rows in both tables... you can then join the result to the actual tables and show the entire rows, or whatever.
/*create environment for testing*/
create table #adr(adrID int identity, fname varchar(20), mname varchar(20), lname varchar(20), radd varchar(20), oadd varchar(20))
insert into #adr(fname, mname, lname, radd, oadd) values ('John', 'M.','Wayne','Houston','New York')
insert into #adr(fname, mname, lname, radd, oadd) values ('Richard', 'Carl','Songowo','Singapore','Houston')
insert into #adr(fname, mname, lname, radd, oadd) values ('Mary', '','Clellan','Sydney','')
insert into #adr(fname, mname, lname, radd, oadd) values ('Samuel', 'Q.','Tripper','London','Paris')
insert into #adr(fname, mname, lname, radd, oadd) values ('Jane', 'Sandra','Little','Toronto','')
create table #adr_b(adrID int identity, fname varchar(20), mname varchar(20), lname varchar(20), radd varchar(20), oadd varchar(20))
insert into #adr_b(fname, mname, lname, radd, oadd) values ('Wayne', 'M.','John', 'New York', 'Houston')
insert into #adr_b(fname, mname, lname, radd, oadd) values ('Richard', NULL,'Bailey', 'Sydney', 'Singapore')
insert into #adr_b(fname, mname, lname, radd, oadd) values ('Ruth', '','Rinford','London','')
insert into #adr_b(fname, mname, lname, radd, oadd) values ('Elizabeth', '','Tripper','Paris','Miami')
insert into #adr_b(fname, mname, lname, radd, oadd) values ('Little', '','Richard','Houston','')
/*show suspicious rows to be checked for possible duplicity*/
SELECT DISTINCT samename.adrID, samename.adrID_b
FROM
(select N.adrID, N_b.adrID as adrID_b
/*compare names*/
FROM
(select adrID, fname as allnames from #adr
UNION ALL
select adrID, mname from #adr
UNION ALL
select adrID, lname from #adr) as N
JOIN
(select adrID, fname as allnames from #adr_b
UNION ALL
select adrID, mname from #adr_b
UNION ALL
select adrID, lname from #adr_b) as N_b
ON N.allnames = N_b.allnames
WHERE N.allnames <> '') AS samename
JOIN
(select A.adrID, A_b.adrID as adrID_b
/*compare addresses*/
FROM
(select adrID, radd as addy from #adr
UNION ALL
select adrID, oadd from #adr) as A
JOIN
(select adrID, radd as addy from #adr_b
UNION ALL
select adrID, oadd from #adr_b) as A_b
ON A.addy = A_b.addy
WHERE A.addy <> '') AS sameadd
/*select those combinations that occur in both queries - by name and by address*/
ON samename.adrID = sameadd.adrID AND samename.adrID_b = sameadd.adrID_b
October 3, 2005 at 8:27 am
SELECT *
FROM a,b
WHERE (A.FNAME = B.FNAME OR A.FNAME = B.MNAME OR A.FNAME = B.LNAME OR
A.MNAME= B.FNAME OR A.MNAME= B.MNAME OR A.MNAME= B.LNAME OR
A.LNAME= B.FNAME OR A.LNAME= B.MNAME OR A.LNAME= B.LNAME)
AND
(A.RADD = B.RADD OR A.RADD = B.OADD OR A.OADD= B.RADD OR A.OADD = B.OADD)
select a.* , b.*
from a INNER JOIN b
on (a.fname in (b.fname,b.mname,b.lname) or a.mname in (b.fname,b.mname,b.lname) or a.lname in (b.fname,b.mname,b.lname))
AND (A.Radd in (B.RADD,B.OADD) OR A.OADD in (B.RADD,B.OADD))
OK, this 2 querys have the same performance because they do the same , the diferences is that the first query is an no implicit INNER JOIN , like we do in oracle.
they have 0.0757 in performance in the SQL server , the only thing that you can do is make individual indexs for each field , fname, lname , mname and addr an oddr.
remenber individual index no a combine index. one index for each record. that would speed alot your query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply