Not in query

[John]

Is there an easy way to return all of the items that are not returned from a
query? I have a working query that returns a group of items, then I want to
query a table for all of the items that do not exist in the first query.

 

[Linda Liu [MSFT]]

Hi John,

I think you may set the where clause in the second query to be the contrary
of the where clause in the first query to do this.

For example, we have a table Persons with two fields PersonID and Name in
it. In the first query, we return all the items that the PersonID less than
100. The sql statement is "select * from Persons where PersonID < 100".

Then we could get the rest items in the table by the sql statement "select
* from Persons where PersonID >= 100".

Hope this helps.
If you have any concerns, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John]

I need to explain better.

Say you have three tables, the first is your persons table, the second is
just to hold ID's from the first and third table (to give you a many-to-many
relationship), and the third table has classes, with 2 fields, classID and
class name.

So one class may have many people and one person may have many classes. The
second table just links them together, ClassID and PersonID.

So now you have a classID and you want to find all the people who are not in
this class. You have to query the second table to find who is in the class,
and then you want to query the People table but only return the people not
in you first query.

Is that the best way to do it? If so, how do you do it. If not, what is?

 

[Pritcham]

Hi

Using your example something like the following would get the results
you need:

Select * From person Where PersonID Not In(SELECT PersonClass.PersonID
FROM PersonClass
WHERE (((PersonClass.ClassID)=2)));

This has been run against a mockup DB with 3 tables: Person (PK:
PersonID, Name), Class(PK: ClassID, Classname), PersonClass(PK:
PersonID + ClassID).

Basically the bracketed Select (subquery) is selecting the 'who is in
class 2' results, the first part of the query is finding people in the
person table who are NOT in the results of that (first) query

Hope that helps
Martin

 

[John]

Yeah, I think its working. Thanks for your help.