How to create a Recordset that joins 3 temp tables

I have an stp where I want to return a Recordset via a SELECT that joins 3 temp tables...

Here's what the temp tables look like (I am being brief)...

CREATE TABLE #Employees (EmpID INTEGER, EmpName NVARCHAR(40))

CREATE TABLE #PayPeriods (PayPeriodIndex INTEGER, StartDate DATETIME, EndDate DATETIME)

CREATE TABLE #Statistics (EmpID INTEGER, PayPeriodIndex INTEGER, HoursWorked REAL)

The #Employees table is populated for all employees.

The #PayPeriods table is populated for each calandar week of the year (PayPeriodIndex=0 to 51).

The #Statistics table is populated from data within another permanent table.

Some employees do not work all 52 weeks.

Some employees do not work any of the 52 weeks.

So, the #Statistics table doesn't have all possible combinations of Employees and PayPeriods.

I want to return a Recordset for all possible combinations of Employees and PayPeriods...

Here's a SELECT that I have used...

SELECT e.EmpId, e.Name, pp.PayPeriodIndex, ISNULL(s.HoursWorked,0)

FROM #Statistics s

LEFT OUTER JOIN #Employees e....

LEFT OUTER JOIN #PayPeriods pp ....

WHERE s.EmpId = e.EmpId

AND s.PayPeriodIndex = pp.PayPeriodIndex

I have had no luck with this SELECT.

Can anybody help?

TIA

I think this is what you want

SELECT e.EmpId, e.EmpName, pp.PayPeriodIndex, ISNULL(s.HoursWorked,0)
FROM #Employees e
CROSS JOIN #PayPeriods pp
LEFT OUTER JOIN #Statistics s
ON e.EmpID = s.EmpID
AND s.PayPeriodIndex = pp.PayPeriodIndex

If you want all employess and all pay periods - you should base your query on the employee table and cross join it to the payperiods table - that should give you all employees and all possible pay period combos.

then you should left outer join to the Stats table to get the hours worked if they exist.

Thanks

AWAL

|||

AWAL,

Thanks alot for your response

I tried the Cross Join and Left Outer Join and I am now generasting the correct recordset...

Thanks again!!!

Dan

|||

There is a lot going on with this report. I can definitely see the solution using the CROSS and LEFT joins; however, I ask the following questions:

When I run this with pay periods that are for future dates I get the future pay periods on the report -- is that wanted?|||

Maybe my questions are off-base. Please forgive me for muddying the water here.

Dave