Subquery help needed! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Subquery help needed!

How can I evaluate more than 2 columns with the where condition? For example, I have table A which has FName, MName, LName (is a unique constraint) which needs to be checked if these combination do not exists in table B, then I will create a new record in table B. The TableB_ID is identity in TableB. Any hints would be really appreciated. Something like these: INSERT INTO TableB(FName, MName, LName, Address)
SELECT a.FName, a.MName, a.LName, a.Address
FROM TableA a
WHERE NOT EXISTS (SELECT b.FName, b.MName, b.LName FROM TableB b) OR
INSERT INTO TableB(FName, MName, LName, Address)
SELECT a.FName, a.MName, a.LName, a.Address
FROM TableA a
WHERE (a.FName NOT IN ( SELECT b.FName FROM TableB b )
AND a.MName NOT IN ( SELECT b.LName FROM TableB b )
AND a.LName NOT IN ( SELECT b.LName FROM TableB b )) Thanks, Name
———
Dilli Grg (1 row(s) affected)
SELECT a.FName, a.MName, a.LName, a.AddressFROM TableA a
WHERE exists
( SELECT 1 FROM TableB b where b.fname = a.FName and b.mname= a.MName and b.lname = a.LName) MohammedU.
Moderator
SQL-Server-Performance.com
quote:Originally posted by MohammedU SELECT a.FName, a.MName, a.LName, a.AddressFROM TableA a
WHERE exists
( SELECT 1 FROM TableB b where b.fname = a.FName and b.mname= a.MName and b.lname = a.LName)
WHERE NOT exists Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

Thanks Roji, I think I miss read…. MohammedU.
Moderator
SQL-Server-Performance.com
Thanks guys for your responses. I used LEFT OUTER JOIN and seems to be working too. Thanks, Name
———
Dilli Grg (1 row(s) affected)
]]>