How to get the reverse of a left join.

Question

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 28-May-20 10:37am

Cody O'Meara

Updated 29-May-20 0:25am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2020-05-28T10:45:00

Solution 1

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()
    )
;

Posted 28-May-20 10:45am

Richard Deeming

Updated 28-May-20 23:50pm

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!

MadMyche · Answer 2 · 2020-05-28T10:47:00

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

Maciej Los · Answer 3 · 2020-05-28T23:14:00

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[^]

How to get the reverse of a left join.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0