[RESOLVED] join 3 tables?

[kebo]

Hey all,
On a MySQL server I have 3 tables. One table is the customers table (table C), and the other 2 tables (tables R & P) measure process timing 2 different ways. Each of these tables has a foreign key pointing back to the customers table and a "minutes" field.

What I need is an SQL statement that returns a table with the company, sum(Table R.minutes) and sum(Table P.minutes) for each customer.

This is a select statement that does run, but gives me the wrong values...

SELECT C.id,
         C.company,
	 FORMAT(sum(R.minutes),2) as 'RM' ,
	 FORMAT(sum(P.minutes),2) as 'BM' FROM customers as C
 JOIN p_table as P ON P.customerID=C.id 
 JOIN r_table as R ON R.customerID=C.id
GROUP BY C.company
ORDER BY C.id

Can anyone show me the correct sql to do this?
thanks
kevin

[GaryMazzone]

Your grouping is wrong you need to GROUP BY C.id and C.company.

[kebo]

Thanks for the feedback, but adding C.ID doesn't change the resulting table unfortunately. Gawd I wish I knew this stuff better. I feel another "learning experience" coming.

[techgnome]

Hard to say w/o knowing what you are getting vs what you think you should be getting...

-tg

[GaryMazzone]

it's a bit of a hack try this:

SELECT 
    R.id,
    ,R.company
    ,R.RM
    ,P.BM
FROM 
    (SELECT 
        C.id,
        C.company,
        FORMAT(sum(R.minutes),2) as 'RM' ,
    FROM customers as C
     JOIN r_table as R ON R.customerID=C.id
    GROUP BY C.id,C.company) AS R
LEFT OUTER JOIN 
    (SELECT C.id,
             C.company,
         FORMAT(sum(P.minutes),2) as 'BM' 
     FROM customers as C
     JOIN p_table as P ON P.customerID=C.id 
     GROUP BY C.id, C.company) As P
    ON R.id = P.id

[kebo]

Hard to say w/o knowing what you are getting vs what you think you should be getting...

yea fair enough...
My C table...

id	company
1	msb
2	mbDemo

My PTable

id	customerID	minutes
1	1	10
2	2	20
3	2	21
4	1	11

My R Table

id	customerID	minutes
1	2	2
2	1	1
3	2	2
4	1	1

Running this query...

select C.id,
         C.company,
         FORMAT(sum(R.minutes),2) as 'RM' ,
         FORMAT(sum(P.minutes),2) as 'PM' from customers as C
 join production_table as P on P.customerID=C.id 
 join runtimetable as R on R.customerID=C.id
Group by  C.id,C.company
order by C.id

Yields this result...

id	company	RM	PM
1	mbs	4.00	42.00
2	mbDemo	8.00	82.00

yet I need it be be

id	company	RM	PM
1	mbs	2.00	21.00
2	mbDemo	4.00	41.00

so that RM and PM are the sums of minutes for each company.

[techgnome]

Actually... the left join rather than the default inner join should work.

SELECT C.id,
         C.company,
	 FORMAT(sum(R.minutes),2) as 'RM' ,
	 FORMAT(sum(P.minutes),2) as 'BM' 
FROM customers as C
LEFT JOIN p_table as P ON P.customerID=C.id 
LEFT JOIN r_table as R ON R.customerID=C.id
GROUP BY C.company
ORDER BY C.id

-tg


edit -we cross posted... AH... yes, your'e getting a cartesian effect there....
I think a variation on Gary's code would work best -- although, his would work, it's not quite how I'd do it... probably just a style call but....

SELECT 
    C.id,
    ,C.company
    ,R.RM
    ,P.BM
FROM Customer C
LEFT OUTER JOIN
    (SELECT 
        CustomerID,
        FORMAT(sum(minutes),2) AS 'RM' ,
    FROM r_table
    GROUP BY customerID) R
  on C.ID = R.CustomerID
LEFT OUTER JOIN 
    (SELECT CustomerID,
         FORMAT(sum(minutes),2) AS 'BM' 
     FROM p_table 
     GROUP BY CustomerID) P
    ON C.id = P.Customerid

-tg

[kebo]

Actually... the left join rather than the default inner join should work.

SELECT C.id,
         C.company,
     FORMAT(sum(R.minutes),2) as 'RM' ,
     FORMAT(sum(P.minutes),2) as 'BM' 
FROM customers as C
LEFT JOIN p_table as P ON P.customerID=C.id 
LEFT JOIN r_table as R ON R.customerID=C.id
GROUP BY C.company
ORDER BY C.id

-tg

That unfortunately yields the same result. Essentially all of the result values are double.

This on the other hand.....

it's a bit of a hack try this:
SQL Code:

1. SELECT
2. R.id,
3. ,R.company
4. ,R.RM
5. ,P.BM
6. FROM
7. (SELECT
8. C.id,
9. C.company,
10. FORMAT(sum(R.minutes),2) AS 'RM' ,
11. FROM customers AS C
12. JOIN r_table AS R ON R.customerID=C.id
13. GROUP BY C.id,C.company) AS R
14. LEFT OUTER JOIN
15. (SELECT C.id,
16. C.company,
17. FORMAT(sum(P.minutes),2) AS 'BM'
18. FROM customers AS C
19. JOIN p_table AS P ON P.customerID=C.id
20. GROUP BY C.id, C.company) AS P
21. ON R.id = P.id

Does work with a little syntax correcting.
I knew this would be a learning experience. I'll spend the next 10-15 figuring out why it work only to get to learn it again in 6 months
Thanks to both
kevin

Here is the final sql that runs....

SELECT 
    R.id, R.company, R.RM, P.BM
FROM 
    (SELECT 
        C.id, C.company, FORMAT(sum(R.minutes),2) AS 'RM' 
     FROM customers AS C
     JOIN r_table AS R ON R.customerID=C.id
     GROUP BY C.id,C.company) AS R
LEFT OUTER JOIN 
    (SELECT C.id, C.company, FORMAT(sum(P.minutes),2) AS 'BM' 
     FROM customers AS C
     JOIN p_table AS P ON P.customerID=C.id 
     GROUP BY C.id, C.company) AS P
    ON R.id = P.id

[GaryMazzone]

What I did was to create 2 derived tables on for each of the actuals. then joined them together for a finial output. The other (orginal) could work I just don't have the time to recreate all the tables and work it out. I don't use MySQL so it is based generic SQL Ansi code