LEFT JOIN/SUM query problem

[Confusion09]

Here are the table structures we'll be working with:

mcd
mcd_referrer (VARCHAR)
mcd_purchase (DECIMAL)

cost
cost_cost (DECIMAL)
cost_referrer (VARCHAR)

mcd_referrer and cost_referrers hold keywords (these are what we'll be matching in the query) and mcd_purchase and cost_cost hold numbers.

$query = mysql_query("SELECT DISTINCT
        mcd.mcd_referrer,
        SUM(mcd.mcd_purchase)/COUNT(mcd.mcd_purchase) AS total_purchase,
        cost.cost_referrer,
        SUM(cost.cost_cost)/COUNT(cost.cost_cost) AS total_cost
    FROM
        cost
    LEFT JOIN
        mcd
    ON
        cost.cost_referrer = mcd.mcd_referrer
    GROUP BY
        cost.cost_referrer");

Let's say these are the records in the tables:

mcd
burger 4.25
burger 4.00
burger 6.00
lettuce
tomato ** .35

cost
burger .10
burger .50
burger 1.00
burger .25
burger .05
lettuce .05
lettuce .10
tomato 1.00
fries 2.00
fries 2.25
bread ** 1.00

I need my query to output this:

keyword purchase cost
bread 0 1.00
burger 14.25 1.90
lettuce 0 .15
fries 4.25 0
tomato .35 1.00

Any ideas on how I can fix it?

[barand]

Create a temp table so you only have single row for each referrer in one of the tables when you join. If you are using MySql 4.1+ you can use a subquery instead of the temp table.

mysql_query("CREATE TEMPORARY TABLE tmp_cost 
			SELECT cost_referrer, SUM(cost_cost) as cost FROM cost
			GROUP BY cost_referrer");

 $query = mysql_query("SELECT 
        tmp_cost.cost_referrer,
        SUM(mcd.mcd_purchase) AS total_purchase,
        tmp_cost.cost AS total_cost
    FROM
        tmp_cost
    LEFT JOIN
        mcd
    ON
        tmp_cost.cost_referrer = mcd.mcd_referrer
    GROUP BY
        tmp_cost.cost_referrer") or die(mysql_error());

 while (list($ref, $purch, $cost) = mysql_fetch_row($query)) {
 	echo "$ref ** $purch ** $cost<br/>";
 }