Query Problem using Mulitple Columns on a Subquery

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 2000 Forums

SQL Server Development (2000)

Query Problem using Mulitple Columns on a Subquery

Author

Topic

KentKir
Starting Member

3 Posts

Posted - 2005-09-23 : 17:58:23

Hello,

I have a question similair, yet not identical, previous post. I need a result set from a query that would look like this:

SELECT table1.col1, table1.col2
FROM table1
WHERE (table1.num, table1.code) NOT IN
(SELECT table2.num, table2.code from table2
where table2.col4 = value

Its a Mutiple Column Subquery with use of the NOT IN Keyword.
Can anyone help?

Thanks

Kent

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-09-23 : 18:12:53

Use NOT EXISTS instead.


SELECT table1.col1, table1.col2
FROM table1
NOT EXISTS(SELECT * FROM table2 WHERE table2.num = table1.num AND table2.code = table1.code AND table2.col4 = value)

Dustin Michaels

KentKir
Starting Member

3 Posts

Posted - 2005-09-23 : 18:29:50

Isn't your main query missing a WHERE clause?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-23 : 18:33:54

You could use a LEFT JOIN:


select
	table1.col1,
	table1.col2
from
	table1
	left join
	table2
	on
		table1.num  = table2.num   and
		table1.code = table2.code  and
		table2.col4 = value
where
	table2.num is null and
	table2.code is null

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-23 : 18:37:17

SELECT t1.col1, t1.col2
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.num = t2.num AND t1.code = t2.code
WHERE t2.col4 = 'value' AND t1.num IS NULL AND t1.code IS NULL

Tara

KentKir
Starting Member

3 Posts

Posted - 2005-09-23 : 18:50:23

Yes, Thank you very much Tara and MVJ!!! That was the result set I was trying to get! Alas, the hours I will not get back trying to figure that one out...

Take Care,

Kent, Livermore California

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-23 : 18:52:41

Geez my connection was slow when it posted my response. When I typed it, it didn't show Michael's yet, so I submitted it. And I got sniped by 4 minutes!

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources