Where rows DO NOT exist? - 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)

Where rows DO NOT exist?

Author

Topic

kporg
Starting Member

5 Posts

Posted - 2007-08-14 : 17:59:48

New to the forum. Any help is greatly appreciated!

Here is what I am trying to do...

table A
custn, ord#
Joe 1
Bob 2
Sally 3
Pete 4
Jill 5

table B
ord# prod#
1 1
1 2
2 1
3 1

I want to return the custn from table A where table B has a prod#=1 but not a prod#=2. So it should only return Bob and Sally.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 18:17:13

[code]
Declare @tableA Table (custn varchar(10), ord int)
Insert into @TableA
Select 'Joe', 1 union all
Select 'Bob' ,2 union all
Select 'Sally', 3 union all
Select 'Pete' ,4 union all
Select 'Jill' ,5

Declare @tableB Table (ord int,prod int)
insert into @TableB
Select 1, 1 union all
Select 1, 2 union all
Select 2, 1 union all
Select 3 ,1

Select A2.*
from (
Select A.Custn, A.ord
from @tableA A
Join @TableB B ON A.Ord = B.Ord
Where B.Prod = 1
) A2 Left Outer Join (Select Ord From @TableB Where Prod = 2) B2 on a2.ord = b2.ord
Where B2.ord is null
[/code]

or

[code]

Select A.Custn, A.ord
from @tableA A
Join @TableB B ON A.Ord = B.Ord
Where B.Prod = 1
And A.Ord Not in (Select Ord From @TableB Where Prod = 2)

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

kporg
Starting Member

5 Posts

Posted - 2007-08-15 : 12:11:34

Thank you very much. I think I got it working with the 2nd query

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-15 : 12:15:03

be carefull with useing not in
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

better to use not exists

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources