[RESOLVED] join 3 tables?

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...

Code:

---

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

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

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.

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

-tg

it's a bit of a hack try this:

SQL Code:

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

Quote:

---

Originally Posted by tg

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...

Code:

---

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.

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

Code:

---

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....

Code:

---

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

Quote:

---

Originally Posted by techgnome

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

Code:

---

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.....

Quote:

---

Originally Posted by GaryMazzone

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:sick:
Thanks to both
kevin

Here is the final sql that runs....

Code:

---

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

---

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