Left Join Won't Return Nulls?

0
votes

I have a Query and a Table. The Query "EmployeeTraining" includes the employee name, and the training course they have taken, date taken and expiry. What I want the LeftJoin to return is ALL the training courses available and the courses that each employee is missing, so in essense, any nulls.

In total there are 5 records in "ApplicableTraining". Some employees have taken only 4/5 of the courses and only has 4 records in the "EmployeeTraining" record. Shouldn't left join return the 5th training item for that particular employee with nulls in the "datetaken" and "Expiry"?

Sample Applicable Training:

  • WHMIS
  • First Aid
  • CPR
  • Propane
  • TDG

Sample EmployeeTraining

  • Sam | WHMIS | 05/03/2011 |05/03/2012
  • Sam | First AID | 06/09/2010 | 06/09/2011
  • Sam | CPR | 05/03/2011 | 05/03/2012
  • Sam | Propane | 12/03/2015| 12/03/2018

Desired Results

  • Sam | WHMIS | 05/03/2011 |05/03/2012
  • Sam | First AID | 06/09/2010 | 06/09/2011
  • Sam | CPR | 05/03/2011 | 05/03/2012
  • Sam | Propane | 12/03/2015| 12/03/2018
  • Sam | TDG | |

This is the left join generated via Design View on Access, but it doesn't return any nulls at all. 

SELECT ApplicableTraining.AppTraining, 
EmployeeTraining.Employee, 
EmployeeTraining.DateTaken, 
EmployeeTraining.Expiry

FROM ApplicableTraining LEFT JOIN EmployeeTraining ON ApplicableTraining.AppTraining = EmployeeTraining.Training;

I uploaded my database here https://drive.google.com/open?id=0B7foIFlbSH78cFpJOHFsYkxiUlU

4
sqlms-accessleft-join
Add some sample table data, and the expected result. – jarlh
Apparently there are no courses without any employees at all. – jarlh
Some employees have taken 4/5 training courses, and has 4 records in the Query. Shouldn't Left Join return the 5th training course, with nulls in the "DateTaken" and Expiry? – Macellaria
Your query should work as it stands in your question, I just tried it. Is there anything you have removed from the query (like a WHERE clause on the Employee)? Do you have a relationship defined between ApplicableTraining and EmployeeTraining? – Andre
The applicableTraining and EmployeeTraining (Query) have a one to many relationship. The EmployeTraining Query, is based off our employee table and 3 different training tables. – Macellaria

4 Answers

2
votes

If that query returns no nulls, then there are no nulls

You can test by adding WHERE EmployeeTraining.Training IS NULL

1
votes

You need to do CROSS JOIN between table:Employee and table:ApplicableTraining after LEFT JOIN with view:EmployeeTraining.

CROSS JOIN, join all records from table A with table B, regardless of connection between their tables.

Sample Applicable Training:

WHMIS
First Aid
CPR
Propane
TDG

Sample Employee

SAM

Result:
SELECT ApplicableTraining.AppTraining, Table[Employee].Employee
FROM ApplicableTraining 
CROSS JOIN Table[Employee]

WHMIS|SAM
First Aid|SAM
CPR|SAM
Propane|SAM
TDG|SAM

Desired Results

SELECT  Table[Employee].Employee, 
        ApplicableTraining.AppTraining, 
        EmployeeTraining.DateTaken, 
        EmployeeTraining.Expiry
FROM ApplicableTraining 
CROSS JOIN Table[Employee]
LEFT JOIN EmployeeTraining ON ApplicableTraining.AppTraining = EmployeeTraining.Training and EmployeeTraining.Employee = Table[Employee]."EmployeeID"

Sam | WHMIS | 05/03/2011 |05/03/2012
Sam | First AID | 06/09/2010 | 06/09/2011
Sam | CPR | 05/03/2011 | 05/03/2012
Sam | Propane | 12/03/2015| 12/03/2018
Sam | TDG | |
0
votes

You are missing the where condition.Try to give aliases for the tables.

SELECT at.AppTraining, 
       et.Employee, 
       et.DateTaken, 
       et.Expiry
FROM ApplicableTraining at
  LEFT JOIN EmployeeTraining et ON at.AppTraining = et.Training
WHERE et.Training IS NULL
0
votes

Try a sanity test using a different existential operator e.g. 

SELECT ApplicableTraining.AppTraining
  FROM ApplicableTraining a
 WHERE NOT EXISTS ( SELECT * 
                      FROM EmployeeTraining a
                     WHERE a.AppTraining = e.Training );

If this returns nothing then there are no missing values.