Left Join Not Returning All Records

  • Thread starter Thread starter Rob Parker
  • Start date Start date
R

Rob Parker

I have a table of staff effort data, containing the following fields:
FY
WBS
Period
Hours

I want a query to return a complete set of records for every period in
each FY. "Simple", I say to myself. I set up another table
containing all possible periods (26 rows, each containing a number
from 1 to 26), and Left-join it to Period in tblStaff Effort. The SQL
of the query is:

SELECT tblStaffEffort.FY, tblStaffEffort.WBS, tblPeriods.Period,
tblStaffEffort.Hours
FROM tblPeriods LEFT JOIN tblStaffEffort ON tblPeriods.Period =
tblStaffEffort.Period;

However, this fails to return a complete set of records; it returns
only the same records as an Inner join.

I'm aware of the problem of left joins not returning all records if
there is a WHERE clause (unless an OR Is Null criteria is used), but
that's not the case here.

So what's my problem, and how do I solve it?

TIA,

Rob
 
What's your definition for "a complete set of records for every period in
each FY" with an example of data?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
What I'm wanting is a set containing the following number of records:
Number of periods in tblPeriods * Number of distinct FYs in
tblStaffEffort * Number of distinct WBSs in tblStaffEffort

And I realise what the problem is: I need left joins to a query which
generates that set of records.

Solved. But thanks for the interest,

Rob
 
Back
Top