CODEWITHSUNDEEP
ms-access
BarclayVision
BarclayVision

Reputation: 863

SQL to return records NOT IN sub query

I am trying to return all records that are not in 

Select UID FROM EnrollmentsTbl WHERE (UID Is Not Null)

The current SQL statement returns all records in linked tables UsersDataTbl and UsersDataCareerTbl

From what I have researched, using an indexed field UID will not produce the results I want, when I use another field like UserName the desired results are rendered:

Is there a work-around to allow me to use indexed field in a sub query?

The following returns all records from the linked tables but does not exclude sub query records:

<asp:AccessDataSource ID="AccessDataSource2" runat="server" 

     DataFile="<%$ ConnectionStrings:AccessSubSiteString %>" 
     SelectCommand="SELECT UsersDataTbl.StudentID, 
                           UsersDataTbl.LastName, UsersDataTbl.FirstName, 
                           UsersDataTbl.UID, UsersDataCareerTbl.Battallion, 
                           UsersDataCareerTbl.Station, UsersDataCareerTbl.Shift
                     FROM UsersDataTbl 
                     LEFT JOIN UsersDataCareerTbl ON UsersDataTbl.UID = UsersDataCareerTbl.UID
                     WHERE (((UsersDataTbl.Career) = True) 
                       AND ((UsersDataTbl.Active) = True) 
                       AND ((UsersDataTbl.UID) NOT IN 
                            (SELECT UID FROM EnrollmentsTbl 
                             WHERE (UID IS NOT NULL)))) 
                     ORDER BY UsersDataTbl.LastName, UsersDataTbl.FirstName">
</asp:AccessDataSource>

The following returns the desired results (but UserName is not a desired or reliable field):

<asp:AccessDataSource ID="AccessDataSource2" runat="server" 
DataFile="<%$ ConnectionStrings:AccessSubSiteString %>" 
SelectCommand="SELECT UsersDataTbl.StudentID, UsersDataTbl.LastName, UsersDataTbl.FirstName, 
UsersDataTbl.UID, UsersDataCareerTbl.Battallion, UsersDataCareerTbl.Station, UsersDataCareerTbl.Shift
FROM UsersDataTbl LEFT JOIN UsersDataCareerTbl ON UsersDataTbl.UID = UsersDataCareerTbl.UID
WHERE (((UsersDataTbl.Career)=True) AND ((UsersDataTbl.Active)=True) AND ((UsersDataTbl.UID) 
Not In (Select UserName FROM EnrollmentsTbl WHERE (UserName Is Not Null)))) 
ORDER BY UsersDataTbl.LastName, UsersDataTbl.FirstName">
</asp:AccessDataSource>

Upvotes: 0

Views: 171

Answers (1)

Yllzarith
Yllzarith

Reputation: 179

You could left join EnrollmentsTbl to UsersDataTbl on UID, then add And EnrollmentsTbl.UID Is Null to your WHERE clause.

Alternatively, you could use a NOT EXISTS clause, though I'd expect poorer performance from an Access db:

 WHERE NOT EXISTS (SELECT NULL FROM EnrollmentsTbl et WHERE et.UID = UsersDataTbl.UID)

Upvotes: 1

Related Questions