CODEWITHSUNDEEP
sqlms-access
Matt Bartlett
Matt Bartlett

Reputation: 360

SQL/query string not returning records that I know exist

I have a table and a query currently in my Access database. The table is of everyone that has completed a piece of work (All Folks Ever Finished) and the work that they have finished. I have a query that has searched another table to find out which people in this table have started another piece of work (People who are are active on another course).

Now I would like to run one more query to pull out the names of people who have completed work but exclude the ones in the query as they have start the next course.

I have put together a query but it doesn't return any records at when I know that it should. 

SELECT
    [All Folks Ever Finished].Level
   ,[All Folks Ever Finished].Status
   ,[All Folks Ever Finished].Fname
   ,[All Folks Ever Finished].Sname
   ,[All Folks Ever Finished].Email
   ,[People who are active on another course].[All Folks Ever Active].Level
FROM
    [All Folks Ever Finished]
INNER JOIN [People who are active on another course]
    ON [All Folks Ever Finished].Email = [People who are active on another course].Email
WHERE
    NOT ( [All Folks Ever Finished].Email ) = ( [People who are active on another course].Email );

I know that there are least 1k records out of 2.5k that should be coming back as being completed but not started another course.

Upvotes: 1

Views: 2140

Answers (3)

Patrick87
Patrick87

Reputation: 28292

Your query can be simplified as follows:

SELECT A.*, B.*
FROM A INNER JOIN b
ON A.Email = B.Email
WHERE Not (A.Email) = (B.Email);

This makes it easy to see the problem: you are joining on Email and then eliminating records based on that same field in the WHERE clause. This will always give you identically the empty set, i.e., no records.

As I understand it, you want people:

  1. Who have finished some work, and
  2. Who are not active on another course.

There are a few ways to do this. One was is by using outer joins:

SELECT A.*. B.*
FROM A LEFT OUTER JOIN B
ON A.Email = B.Email
WHERE B.Email is NULL

This works by doing the outer join and selecting only those people who have finished work whose entry in having completed work is empty.

You can also do this using set difference by e.g. MINUS:

SELECT A.*
FROM A
MINUS
SELECT A*
FROM A INNER JOIN B
ON A.Email = B.Email

Upvotes: 2

Filipe Martins
Filipe Martins

Reputation: 608

Try this code:

SELECT [All Folks Ever Finished].Level, [All Folks Ever Finished].Status, [All Folks Ever Finished].Fname, [All Folks Ever Finished].Sname, [All Folks Ever Finished].Email, [People who are active on another course].[All Folks Ever Active].Level
FROM [All Folks Ever Finished] LEFT JOIN [People who are active on another course] ON [All Folks Ever Finished].Email = [People who are active on another course].Email
WHERE ([People who are active on another course].Email is null);

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Your inner join condition is the opposite of your where clause, so you will not get any rows. If you had a different on clause, then you might be able to get some rows back.

To find people in the first query/table who are not in the second query/table, you can use a left join and return rows where the second table is null.

SELECT 
   [All Folks Ever Finished].level
 , [All Folks Ever Finished].status
 , [All Folks Ever Finished].Fname
 , [All Folks Ever Finished].Sname
 , [All Folks Ever Finished].Email
 , [People who are active on another course].[All Folks Ever Active].level
from [All Folks Ever Finished]
  left join [People who are active on another course] 
    on [All Folks Ever Finished].Email = [People who are active on another course].Email
where [People who are active on another course].Email is null;

Upvotes: 2

Related Questions