CODEWITHSUNDEEP
sqlms-access
maneesha
maneesha

Reputation: 685

Building a query by excluding the results of another query

I'm designing a database in Access 2010.

I have this query that works as I want it to:

    SELECT Participants.ParticipantID, [FirstName]+' '+[LastName] AS Participant
    FROM Participants
    WHERE (((Participants.SiteName)=forms!DailyWorkshops!SiteName) And 
((Participants.YearLookup)=forms!DailyWorkshops!YearLookup))
    ORDER BY Participants.FirstName, Participants.LastName;

Now I want another query that gives me everything else.

ie

    SELECT Participants.ParticipantID, [FirstName]+' '+[LastName] AS Participant
    FROM Participants

    WHERE 

    **exclude all these results 
    (((Participants.SiteName)=forms!DailyWorkshops!SiteName) And
 ((Participants.YearLookup)=forms!DailyWorkshops!YearLookup))**


    ORDER BY Participants.FirstName, Participants.LastName;

This seems to work but I was wondering, is this the most straightforward way to do this?

    SELECT Participants.ParticipantID, [FirstName]+' '+[LastName] AS Participant
    FROM Participants
    WHERE Participants.ParticipantID NOT IN 

    (SELECT Participants.ParticipantID FROM Participants WHERE 
     (((Participants.SiteName)=forms!DailyWorkshops!SiteName) And 
((Participants.YearLookup)=forms!DailyWorkshops!YearLookup)))
    ORDER BY Participants.FirstName, Participants.LastName;

Upvotes: 1

Views: 5312

Answers (2)

mikeY
mikeY

Reputation: 519

I think in general that this form:

SELECT field
from list_a
where field not in (select field from list_b)

would go as this form:

SELECT a.field
from list_a a left join (select field from list_b) b on a.field=b.field
where b.field is NULL

I think the JOIN is faster than the NOT IN.

EDIT: Changed to show the join on a select instead of a table.

Edit: I don't understand why this answer was down voted.

Upvotes: 1

Stephen Turner
Stephen Turner

Reputation: 7314

Why is this not very simple...

SELECT Participants.ParticipantID, [FirstName]+' '+[LastName] AS Participant
FROM Participants
WHERE 
    NOT 
       (((Participants.SiteName)=forms!DailyWorkshops!SiteName) And
        ((Participants.YearLookup)=forms!DailyWorkshops!YearLookup))
ORDER BY Participants.FirstName, Participants.LastName;

The only reason may be because of nulls in your columns, which you can fix with: 

SELECT Participants.ParticipantID, [FirstName]+' '+[LastName] AS Participant
FROM Participants
WHERE 
    NOT 
       (((Participants.SiteName)=forms!DailyWorkshops!SiteName OR
             IsNull(Participants.SiteName)) And
        ((Participants.YearLookup)=forms!DailyWorkshops!YearLookup OR
             IsNull(Participants.YearLookup)))
ORDER BY Participants.FirstName, Participants.LastName;

Upvotes: 1

Related Questions