April 19, 2011 at 12:11 pm
Is there a trick or common way to do the following:
Let's say I have a table called People with two fields, PersonID and Status
PersonID, Status
1111111, A
1111111, I
1111111, P
1234567, P
I want it so that if my query finds a status of 'A' across a person, the person as a whole is excluded.
If I write a query like
SELECT *
FROM People
WHERE Status <> 'A'
I'll return:
1111111, I
1111111, P
1234567, P
which makes perfect sense. I'm just wondering if there's a slick way to do that, perhaps using a case statement/group by/something along those lines.
Ideally, I'd only return:
1234567, P
The best way I can think to do that now would be something like the following:
SELECT *
FROM People
WHERE PersonID NOT IN (SELECT PersonID FROM People WHERE Status = 'A')
I'm currently joining to the People table and I'd rather not use a derived table like the one above if I don't have to, just because it gets a little messy. So, just wondering if there are better ways out there to accomplish what I'm looking to accomplish.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
April 19, 2011 at 12:34 pm
What would be the reason for not wanting to use <>'A' That would certainly the most direct.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 19, 2011 at 12:43 pm
<> 'A' would only exclude the one record where Status = 'A'. I want to exclude the entire person if any of his/her records have a status = 'A'.
Mike Scalise, PMP
https://www.michaelscalise.com
April 19, 2011 at 12:58 pm
In that case the way you have demonstrated is the way I would likely do it unless we are talking about an enourmous amount of data. In that case I might put the records in a temp table and use a join. you could also use a CTE.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 19, 2011 at 3:28 pm
A derived table may not perform as well as an EXISTence check using a correlated subquery. Try something like this:
SELECT p.*
FROM People p
WHERE NOT EXISTS ( SELECT *
FROM People
WHERE PersonID = p.PersonID
AND Status = 'A' )
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 28, 2012 at 4:04 am
HI mikes84,
Have you got the your satisfied solution for this requirement..if any please post here.
I am also looking for the samething.
But indeed need to go with what OPC.THREE has suggested above..
May 30, 2012 at 6:36 am
sunilk, yes -- the solution I used was the one opc suggested. It did exactly what I was looking for. I would use that.
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply