[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