Click here to Skip to main content
15,881,413 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have the following below. This selects the Common data between 2 tables. Now I need to get the reverse, select anything that is not in "results" but is in the "employees" table.

PHP
SELECT 
    distinct fullName
FROM 
    results 
LEFT JOIN employees
    ON name
    where dateTime > CURDATE()


Doing this selects the rows correctly I compared them. So the reverse should be the leftover in employees.

EDIT:
a example:
Results table:
ABC 1999-05-28
ABCDEF 2020-05-28
Cody 1999-05-28

Employees table:
ABC
Cody
Ted
ABCDEF


After using the dateTime = CURDATE() which is 2020-05-28, I need it display

ABC
Cody
Ted

as there was no results submitted on the CURDATE(). Since ABCDEF in on the CURDATE() it does not need to display.

I guess I should add some background. At our place of work, every morning a user fills out a daily health assessment. We want to be able to see who did not fill out the assessment that day, just for reporting.

What I have tried:

I tried doing exactly the reverse but I'm getting 0 results when I know there should be some:
PHP
SELECT 
    name
FROM 
    employees
left JOIN results
    ON  fullName
    where dateTime >CURDATE()
Posted
Updated 29-May-20 0:25am
v2

Assuming the dateTime column is in the results table, your WHERE clause filters out any results which don't have a match. Those results will have NULL in the dateTime column.

You can either move the filter to the join condition:
SQL
SELECT
    employees.name
FROM
    employees
    LEFT JOIN results
    ON results.fullName = employees.name
    And results.dateTime > CURDATE()
WHERE
    results.fullName Is Null
;
Or use a Not Exists clause:
SQL
SELECT
    employees.name
FROM
    employees
WHERE
    Not Exists
    (
        SELECT 1
        FROM results
        WHERE results.fullName = employees.name
        And results.dateTime > CURDATE()
    )
;
 
Share this answer
 
v2
Comments
Cody O'Meara 28-May-20 16:57pm    
Thank you for your help! Unfortunately this doesn't seem to be working. For more of an explanation. In my 'employees' table, I have 100 records. In my 'results' table, I have 75 records(after the where 'dateTime' clause). I want to display the 25 records that is not in the table'

On the 'DateTime' subject, In my results table, I have 500+ records and about 75 added every day. The 'Datetime' filters it to only include today's results. Maybe I need to add where clause somewhere else?

Thanks again for your response!
Cody O'Meara 28-May-20 17:11pm    
a example:
Results table:
ABC 1999-05-28
ABCDEF 2020-05-28
Cody 1999-05-28

Employees table:
ABC
Cody
Ted
ABCDEF


After using the dateTime = CURDATE() which is 2020-05-28, I need it display

ABC
Cody
Ted

as there was no results submitted on the CURDATE(). Since ABCDEF in on the CURDATE() it does not need to display.

I guess I should add some background. At our place of work, every morning a user fills out a daily health assessment. We want to be able to see who did not fill out the assessment that day, just for reporting.
Richard Deeming 29-May-20 5:49am    
You need to add a filter to the first query to remove records which have a match in the results table.

Once you've done that, changing results.dateTime > CURDATE() to results.dateTime = CURDATE() in either query should give you the desired results.

SQL Fiddle[^]
Maciej Los 29-May-20 4:47am    
5ed!

I would probably start off with just a WHERE clause with the NOT IN operator like this
SQL
SELECT Distinct fullName
FROM   Results 
WHERE  Name NOT IN (SELECT Name FROM Employees)
AND    dateTime > CURDATE()

It looks like what you want could work with this.... but without decent the schema, sample data, or a clear understanding of EXACTLY what you want I cannot be sure
As evidenced by two credible people who have tried to help you
SQL
SELECT Distinct fullName
FROM   Employees 
WHERE  fullName NOT IN (
	SELECT Name
	FROM   Results)
	WHERE  dateTime = CURDATE()
)

Now.... it seems the results the original query you had was using a greater than comparison when it should have been a simple equals statement
 
Share this answer
 
v2
Comments
Cody O'Meara 28-May-20 17:21pm    
Thank you for the response. Unfortunately I'm coming up with 0 results
Cody O'Meara 28-May-20 17:31pm    
Here is a link to show my 2 tables. The ones highlighted should be displayed as they are not in the fullName table.

https://gyazo.com/66a84d81c7214cfc7d0dd9a41962bce0
MadMyche 28-May-20 19:12pm    
Updating the "answer"
Maciej Los 29-May-20 4:47am    
5ed!
Check this out:
SQL
CREATE TABLE results
(
  fullname varchar(50),
  somedate datetime
);

CREATE TABLE employees
(
  fullname varchar(50)
);

INSERT INTO results(fullname, somedate)
VALUES('ABC', '1999-05-28'),
('ABCDEF', '2020-05-28'),
('Cody', '1999-05-28');

INSERT INTO employees(fullname)
VALUES('ABC'), ('Cody'), ('Ted'), ('ABCDEF');

SELECT e.* 
FROM employees e 
WHERE NOT EXISTS
(
	SELECT 1
	FROM results r1
	WHERE r1.somedate = '2020-05-28' AND r1.fullname = e.fullname
) 



SQL Fiddle[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900