Running a query to yield blank/non existent data

[jrsarrat]

Hello!
I have been struggling with producing a query that will pull all data from one field in a table that doesn’t exist in another table.
I have two tables. The first table holds all employee information. The second table holds log in/log out information.
I want the query to pull the employees whom do not exist in table 2, but do exist in table 1.
Your help is much appreciated.
Thank you!

 

[xenou]

You can just use an exists clause (which is ironically exactly what you are asking for):

select * 
from Table1 
where not exists (
    select * from Table2 where Table2.EmpNum = Table1.EmployeeNum)

 

[jrsarrat]

You can just use an exists clause (which is ironically exactly what you are asking for):

select * 
from Table1 
where not exists (
    select * from Table2 where Table2.EmpNum = Table1.EmployeeNum)

Hello! Thank you for your reply.
“The query returns this message:
You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query’s FROM clause. Revise the SELECT statement of the subquery to request only one field.”
Help me understand this please?

 

[xenou]

Hi, please post your query so we can see what you did.

 

[jrsarrat]

Good morning! Sure.

SELECT *
FROM Emp_tbl
WHERE NOT EXIST (
SELECT * FROM tblEmpLogin where tblEmpLogin.pf_id = Emp_tbl.pf_id);

 

[xenou]

Hi,
in your query you have the word EXIST

This should say EXISTS

 

[jrsarrat]

Hi,
in your query you have the word EXIST

This should say EXISTS

OMG! :0) Thank you! Issue solved!!!

 

[jrsarrat]

Hi,
in your query you have the word EXIST

This should say EXISTS

Good morning!

This morning, I adjusted the query to pull select fields and to only give me data where Date(). Unfortunately, the query returns 0 rows. Also I do not receive an error message when the query is ran, so am I on the right track? The statement is below:

SELECT tblEmpLogin.EmpFName, tblEmpLogin.EmpLName, tblEmpLogin.Team_Lead1, tblEmpLogin.Role1, tblEmpLogin.Dte1
FROM tblEmpLogin
WHERE (((tblEmpLogin.Dte1)=Date()) AND ((Exists (SELECT Emp_tbl.Emp_FName, Emp_tbl.Emp_LName, Emp_tbl.Team_Lead, Emp_tbl.Role FROM Emp_tbl where Emp_tbl.pf_id = tblEmpLogin.pf_id))=False));

 

[xenou]

You need to check your data to see if records on that date exists. If your query returns nothing that could either mean that this is the correct result or you haven't written your query correctly (which is true of any query basically - you always need to test thoroughly if you have any doubt).

You have also added things to the exists clause that don't need to be there. Exists is not used with = true or = false. Just use exists or not exists as needed. There is also no reason for a column list in an exists clause since it doesn't select columns. It only needs to check for the existence or non-existence of at least one matching row.

This is a cleaned up version of your query:

SELECT 
	T.EmpFName, 
	T.EmpLName, 
	T.Team_Lead1, 
	T.Role1, 
	T.Dte1
FROM 
	tblEmpLogin T
WHERE 
	(
		(T.Dte1=Date()) 
		AND 
		(Exists (SELECT * FROM Emp_tbl where Emp_tbl.pf_id = T.pf_id))
	);

 

[jrsarrat]

You need to check your data to see if records on that date exists. If your query returns nothing that could either mean that this is the correct result or you haven't written your query correctly (which is true of any query basically - you always need to test thoroughly if you have any doubt).

You have also added things to the exists clause that don't need to be there. Exists is not used with = true or = false. Just use exists or not exists as needed. There is also no reason for a column list in an exists clause since it doesn't select columns. It only needs to check for the existence or non-existence of at least one matching row.

This is a cleaned up version of your query:

SELECT 
    T.EmpFName, 
    T.EmpLName, 
    T.Team_Lead1, 
    T.Role1, 
    T.Dte1
FROM 
    tblEmpLogin T
WHERE 
    (
        (T.Dte1=Date()) 
        AND 
        (Exists (SELECT * FROM Emp_tbl where Emp_tbl.pf_id = T.pf_id))
    );

Hello! Thank you so much for assisting me. The query above does not work either. Below is another adjustment I made (it worked!)... It seems as though Emp_tbl needs to be Table 1. The drawback with that is Emp_tbl only stores employee info (no Date field). I need this criteria for report purposes. Is there an alternative? Thank you for handing in there with me!

SELECT
T.Emp_FName,
T.Emp_LName,
T.Team_Lead,
T.Role
FROM
Emp_tbl T
WHERE
(
(Not Exists (SELECT * FROM tblEmpLogin where tblEmpLogin.pf_id = T.pf_id))
);