Crazy Join Query

[edmana]

Hey all,

I have a real doozie of a query here. I have 4 tables:

tblProjects
ProjectKeyId
ProjectDescription

tblPriceBookLabor
ItemID
Description

tblEstimatedLabor
ProjectID
ItemID
TotalCost

tblActual Labor
ProjectID
ItemID
TotalCost

tblProjects is the table from which I will link. tblPriceBookLabor shows a list of all labor items. tblEstimatedLabor and tblActualLabor contains many line items per project that repeat (multiple items of labor can repeat). What I am looking to do is provide the master list from tblPriceBookLabor and show the corresponding Estimated and Actaul Costs. For example:

tblProjects
ProjectID ProjectDescription
1 Project 1
2 Project 2

tblPriceBookLabor
ItemID Description
LABOR1 Labor Type 1
LABOR2 Labor Type 2
LABOR3 Labor Type 3
LABOR4 Labor Type 4
LABOR5 Labor Type 5
LAOBR6 Labor Type 6

tblEstimatedLabor
ItemID TotalCost ProjectID
LABOR1 1,000 1
LABOR2 500 1
LABOR3 500 1
LABOR1 1,000 1

tblActualLabor
ItemID TotalCost ProjectID
LABOR1 1,500 1
LABOR4 500 1
LABOR5 500 1
LABOR5 500 1
LABOR5 500 1

The results would look like this:

ItemID TotalEstimated Total Actual ProjectID
LABOR1 2,000 1,500 1
LABOR2 500 0 1
LABOR3 500 0 1
LABOR4 0 500 1
LABOR5 0 1500 1
LABOR6 0 0 1

I was able to do a left outer join using the tblPriceBookLabor table and tblEstimatedLabor. This gave me all in the price book table. However, when I then put in the where clause for the ProjectID, I only received the relavent items from the estimated table, not all rows as I would expect.

Anyone have any suggestions or ideas?

Thanks!
Ed

 

[edmana]

George,

You ABSOLUTELY ROCK! The SP worked great, although I cannot take credit for creating one, just hitting the execute button.

This report took like 25 seconds to run previously. It now runs in 4 seconds!!!!

Thanks so much!
Ed