SubSelect using Not In - 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

General SQL Server Forums

New to SQL Server Programming

SubSelect using Not In

Author

Topic

djfiii
Starting Member

13 Posts

Posted - 2007-01-24 : 14:28:15

I have two tables with the following relevant fields:

Apps

appID
appName

PBC

pbcID
appID
appCT

These are joined on appID. appCT can be 1 of 2 values, either "PC" or "LA". So an example of a few records in PBC would be:

1 1 PC
2 1 LA
3 2 PC
4 2 LA
5 3 PC
6 4 LA
...
...

You can see that for each App, in PBC there can be two related records - PC and LA. But for example, record number 5 is App 3 PC, but there is no App 3 LA. I am trying to build a select to tell me which Apps are not in PBC at all, AND which Apps only have either LA or PC, not both.

Help?

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-24 : 14:37:36

select t1.appID, min(coalesce(t2.appCT,''))
from Apps t
left join PBC t2
on t1.appID = t2.appID
group by t1.appID
having count(*) <> 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-24 : 14:41:15

[code]create table #Apps (appID int, [appName] varchar(50))
create table #PBC (pbcID int, appID int, appCT char(2))
insert #Apps
select 1, 'App one' union all
select 2, 'App two' union all
select 3, 'App three' union all
select 4, 'App four' union all
select 5, 'App five'
insert #PBC
select 1, 1, 'PC' union all
select 2, 1, 'LA' union all
select 3, 2, 'PC' union all
select 4, 2, 'LA' union all
select 5, 3, 'PC' union all
select 6, 4, 'LA'

select A.appID, A.[appName],
case count(P.appID)
when 0 then 'Not in PCB'
when 1 then min(P.appCT) + ' only'
when 2 then 'both'
end
from #Apps A
left outer join #PBC P on P.appId = A.appID
group by A.appID, A.[appName]
order by A.appID

drop table #Apps
drop table #PBC[/code]

djfiii
Starting Member

13 Posts

Posted - 2007-01-24 : 15:57:58

thanks to both of you!

djfiii
Starting Member

13 Posts

Posted - 2007-01-24 : 16:57:47

okay, sorry for the additional post, however I posted my original question with the assumption that I could add a where clause to whatever solution was presented, but for reasons unknown to me, I am unable to do that.

There is in fact another field in PBC - auditID. So all of the previous still applies, with the exception that I need to say "where auditID = XXX". When I try that, the result set shrinks (presumably because the presented queries both assume there will be at most 2 occurrences of appID in table PBC - however there should be at most 2 occurrences within table PBC where auditID=XXX). Hope that makes sense?

I have accomplished what I need with two queries, and iterating through a nested loop, but if I can get the query right, I can eliminate that unnecessary drain on the server.

Again, the assistance is appreciated.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-24 : 23:37:54

Not exactly sure what you were asking, but I think this is what you want.

create table #Apps (appID int, [appName] varchar(50))
create table #PBC (pbcID int, appID int, appCT char(2), auditID int)
insert #Apps 
select 1, 'App one' union all
select 2, 'App two' union all 
select 3, 'App three' union all 
select 4, 'App four' union all
select 5, 'App five'
insert #PBC
select 1, 1, 'PC', 111 union all
select 2, 1, 'LA', 555 union all
select 3, 2, 'PC', 111 union all
select 4, 2, 'LA', 555 union all
select 5, 3, 'PC', 555 union all
select 6, 3, 'LA', 555 union all
select 7, 3, 'YY', 555 union all
select 8, 4, 'LA', 555
 
select A.appID, A.[appName], 
	case count(P.appID) 
	when 0 then 'Not in PCB' 
	when 1 then min(P.appCT) + ' only'
	else 'multiple'
	end
from #Apps A
left outer join #PBC P on P.appId = A.appID
where P.auditID = 555
group by A.appID, A.[appName]
order by A.appID

drop table #Apps
drop table #PBC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources