using case in mysql

Hi,


I have 3 tables with the following structures in the same database.

fedex_shipping //has all the shipping info
{
fedex_shipping,
order_site, //either ='site1' or 'site2'
cust_po
}

orders_shipping_site1
{
orders_id,
shipping_cost,
}

orders_shipping_site2
{
orders_id,
shipping_cost,
}

Need to find all the shipping info from 2 tables,orders_shipping_site1 and orders_shipping_site2, and compare them with fedex_shipping info. If the order_site = site1 then find the matching info in site1 shipping table otherwise find the matching info in site2 table.


The query doesn't seem to be working (not sure it is logic or syntax error). Thanks for any tip or help in advance.

$result_report = mysql_query("SELECT f.fedex_shipping,
CASE WHEN f.order_site = 'site1'
THEN (site1.shipping_cost - f.fedex_shipping)) ELSE (site2.shipping_cost - f.fedex_shipping) END as difference,
FROM fedex_shipping as f,
orders_shipping_site1 as site1,
orders_shipping_site2 as site2
WHERE CASE WHEN f.order_site = 'site1' THEN cust_po = site1.orders_id ELSE cust_po = site2.orders_id END ORDER BY cust_po DESC ",$con);