How to join & sum (cartesian join problem) 1

[donjohnson]

Hi! I am new to SQL, and have done some easy queries, but have a problem I can't get past - thanks for your help in advance.

Here's the deal:
Table H contains records with hours columns (QA, IT, Vac, HR), with a key of Date, Site and Shift (one rec/Date,site,shift)

Table D contains recs with a detail of production hours (P_Hours) for each task , using the key Date, Site, Shift and taskid

I need to produce a query recordset that contains a sum of d.P_hours, sum of each h column (h.QA, H.IT, h.VAC, h.HR), a calculation of (h.QA+h.IT+h.Vac+h.HR)/(d.P_Hours), for each Date and Site.

Everything I have tried has produced a cartesian join, and I am at a loss.

Can someone help me here?

 

[PruSQLer]

Cartesians are the result of improper joins so why don't you post your joins?

 

[donjohnson]

Here are the two distinct queries:

SELECT     Production_Date AS PDate, Location AS Site, SUM(Hours) AS Prodhours
FROM         production_details
WHERE     (Production_Date = '10/18/2004')
GROUP BY Production_Date, Location

and

SELECT     Payroll_Date AS Pdate, Location AS Site, SUM(TSR_Hours) AS TSR, SUM(IT_Hours) AS IT, SUM(Lic_Hours) AS Lic, SUM(HR_Hours) AS HR, SUM(QA_Hours) AS QA, SUM(Vac_Hours) AS Vac
FROM         payroll_hours
WHERE     (Payroll_Date = '10/18/2004')
GROUP BY Payroll_Date, Location

Each of these queries produces three rows, one for each site, with the correct totals.

There will always be matching data to join at the date/location level, so no outer join is needed.

What I want to do is join these two queries to get one set of three rows. So after I do a simple merge of these:

SELECT     ph.Payroll_Date AS Pdate, ph.Location AS Site, SUM(ph.TSR_Hours) AS TSR, SUM(ph.IT_Hours) AS IT, SUM(ph.Lic_Hours) AS Lic, SUM(ph.HR_Hours) AS HR, SUM(ph.QA_Hours) AS QA, SUM(ph.Vac_Hours) AS Vac, SUM(pd.Hours) AS Prodhours
FROM         payroll_hours ph INNER JOIN
                      production_details pd ON ph.Payroll_Date = pd.Production_Date AND ph.Location = pd.Location
WHERE     (ph.Payroll_Date = '10/18/2004')
GROUP BY ph.Payroll_Date, ph.Location

This is where I get the cartesian product. There are 52 records for location 1 in the production_Details table, and 2 records for that location in the payroll_hours table for the selected date. The end result seems to have cartesian joined the records before doing the sum() and group by, rather than after.

Hopefully you have enough to go on to set me straight.

Thanks!

Don

 

[PHV]

You may try this:
SELECT ph.Pdate, ph.Site, ph.TSR, ph.IT, ph.Lic, ph.HR, ph.QA, ph.Vac, pd.Prodhours
FROM (
SELECT Payroll_Date AS Pdate, Location AS Site, SUM(TSR_Hours) AS TSR, SUM(IT_Hours) AS IT, SUM(Lic_Hours) AS Lic, SUM(HR_Hours) AS HR, SUM(QA_Hours) AS QA, SUM(Vac_Hours) AS Vac
FROM payroll_hours WHERE Payroll_Date='10/18/2004'
GROUP BY Payroll_Date, Location
) ph INNER JOIN (
SELECT Production_Date AS PDate, Location AS Site, SUM(Hours) AS Prodhours
FROM production_details WHERE Production_Date='10/18/2004'
GROUP BY Production_Date, Location
) pd ON ph.Pdate=pd.Pdate AND ph.Site=pd.Site

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[donjohnson]

Wow!

Thanks for the help! I knew it couldn't be <too> difficult, but that it was over my head. I hope I can help someone down the road someday!

Don