Table Joining?

Can anyone please help me with this simple(?) table joining method for gathering production machinery information.

I'm trying to pull data from 3 tables, all with a common field (machineid).
Table are:

machines - (machineid, machinename)
upgrades - (machineid, cost)
StockIssues - (machineid, cost)

I want to create a result showing - machinename, machineid, upgrades.cost, StockIssues.cost.

Not every machine has upgrades or StockIssues, but all I'm getting from my code is a mess!

Here's what I have. Any help much appreciated thanks.


SELECT m.machinename, m.machineid, SUM(u.cost) as upgrades, SUM(s.cost) as Stock
FROM machines as m, upgrades as u, StockIssues as s
WHERE u.machineid= m.machineid or s.machineid = m.machineid
group by m.machineid

 

You need to use a LEFT Join (http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html) to return all records from machines without regard for matching records in StockIssues and upgrades.

Below is what your query should be:

SELECT machines.machinename, machines.machineid, Sum(upgrades.cost) AS upgrades, Sum(StockIssues.cost) AS Stock
FROM (machines LEFT JOIN StockIssues ON machines.machineid = StockIssues.machineid) LEFT JOIN upgrades ON machines.machineid = upgrades.machineid
GROUP BY machines.machinename, machines.machineid;

PHP:

 

Thanks so much plog for taking the time, there is something still not right though.
The results are multiuplying themselves by the amount of records in the other column.
eg. where a machine has 2 StockIssues and 1 upgrade, the upgrades are multiplied by 2, (if upgrade.cost is $200 in shows $400) and vice versa. I've been trying to figure it out but struggling again. Any ideas plog or anyone?
Thanks.

 

Sorry for the almost correct answer. You do need a Left Join, but because you want totals from 2 tables you need to run some sub-queries to do that--otherwise you will overcount some totals.

Here is the code you need:

SELECT machines.machineid, machines.machinename, totSI.sicost AS Stock, totU.ucost AS Upgrades
FROM 
	(machines LEFT JOIN 
		(SELECT StockIssues.machineid, Sum(StockIssues.cost) AS sicost FROM StockIssues GROUP BY StockIssues.machineid)  AS totSI 
			ON machines.machineid = totSI.machineid) 
		LEFT JOIN 
		(SELECT upgrades.machineid, Sum(upgrades.cost) AS ucost FROM upgrades GROUP BY upgrades.machineid) AS totU
			ON machines.machineid = totU.machineid;	

PHP:

Essentially it totals up the StockIssues and Upgrades tables by themselves, then pulls those totals together by using the machines table.

 

Wow man, there's no way I would've got there. Thanks for the time and the lesson mate. I need to add a few more tables into the result to generate a multi-series chart, but I should be able to suss that out from what you've given me.
Thanks again.
Jamie.