Macellaria Macellaria - 2 months ago 9
SQL Question

Left Join Won't Return Nulls?

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

Rit Rit
Answer

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 | |