Correlated subquery help! - SQL Server Forums

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)

Correlated subquery help!

Author

Topic

DilliGrg
Starting Member

7 Posts

Posted - 2007-03-01 : 18:03:22

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. 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)


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)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-01 : 19:01:29

You can do that with a LEFT OUTER JOIN:


DECLARE @TableA TABLE(FName VARCHAR(100), MName VARCHAR(100), LName VARCHAR(100), Address VARCHAR(100))
DECLARE @TableB TABLE(FName VARCHAR(100), MName VARCHAR(100), LName VARCHAR(100), Address VARCHAR(100))

INSERT @TableA
SELECT 'Bob', 'Q', 'Smith', 'address1' UNION ALL
SELECT 'Frank', 'D', 'Miller', 'address2' UNION ALL
SELECT 'Ted', 'U', 'Peet', 'address3' UNION ALL
SELECT 'Craven', 'J', 'Moorehead', 'address4'

INSERT @TableB
SELECT 'Bob', 'Q', 'Smith', 'address1' UNION ALL
SELECT 'Frank', 'D', 'Miller', 'address2' UNION ALL
SELECT 'Jake', 'B', 'Williams', 'address5' UNION ALL
SELECT 'Randy', 'F', 'Beatty', 'address6' 


INSERT 
	@TableB(FName, MName, LName, Address)
SELECT 
	a.FName, a.MName, a.LName, a.Address
FROM 
	@TableA a
LEFT OUTER JOIN
	@TableB b
	ON a.FName = b.FName
	AND a.MName = b.MName
	AND a.LName = b.LName
	AND a.Address = b.Address
WHERE
	b.FName IS NULL


SELECT *
FROM @TableB

DilliGrg
Starting Member

7 Posts

Posted - 2007-03-01 : 19:25:00

Thank you Lamprey, seems to be working. I wasn't sure to join on FName and filter only on (b.FName IS NULL) that for left join since I was thinking that FName, MName and LName combination should be checked. Thanks again.


Thanks,

Name
---------   
Dilli Grg     

(1 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-01 : 19:37:44

DilliGrg,

The b.FName IS NULL doesn't mean that you have data in that column. It's just that you are doing a LEFT JOIN, which means grab all rows that mean the join criteria plus all of the rows in the left table that don't meet the criteria. When there are rows in the left table that don't meet the join criteria, NULLs are put for the right table columns.

Let us know if that isn't clear. Here's an example:

Table1
Column1
Column2

Table2
Column1
Column99

Sample rows from each (separated by a comma to make posting this quick)

Table1
1, 'Tara'
2, 'Michael'
3, 'Alex'

Table2
1, 'SQLTeam'
2, 'SomeSite'

So if we do a LEFT JOIN Table1 ON Table1.Column1 = Table2.Column1, we will see this:

Column1, Column2, Column99
1, 'Tara', 'SQLTeam'
2, 'Michael', 'SomeSite'
3, 'Alex', NULL

Note how there aren't any NULLs in Column99 in the sample data provided.

Tara Kizer

DilliGrg
Starting Member

7 Posts

Posted - 2007-03-02 : 12:02:15

Thanks guys. Here is what other solution might look like:


INSERT INTO TableB(FName, MName, LName, Address)
SELECT a.FName, a.MName, a.LName, a.Address
FROM TableA a
WHERE NOT EXISTS
	( SELECT 1 FROM TableB b 
		WHERE b.fname = a.FName 
		AND b.mname= a.MName 
		AND b.lname = a.LName)


Thanks,

Name
---------   
Dilli Grg     

(1 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-02 : 12:10:24

The left outer join solution should be used instead of the exists solution. Joins should be more efficient.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources