choice of join?!

[lifeworks]

Hi

Trying to pull this off in one query but my and right & left joining is giving me unusual results...

i have a 'product_style' table, an 'product_item' table, an 'addto' table and a 'removefrom' table - im monitoring when products are added/removed to/from the cart... and want to pull a report for each product on how many times they're added, removed...

the link goes product -> item ->add_to/remove_from ie. the add_to, remove_from table records items, and each item is associated to a product_style...

my latest try was:

$sql = "SELECT s.name,COUNT(a.id),COUNT(r.id) FROM product_items AS i 
LEFT JOIN watch_addtocart AS a ON a.id = i.id 
LEFT JOIN watch_removefromcart AS r ON r.id = i.id 
LEFT JOIN product_styles AS s ON s.id = i.product_style_id 
GROUP BY i.product_style_id";

but theres only one record in the addto table, and it produces 2 or 3 for random product...?!

[urtrivedi]

Your query seems to be ok, though I have changed last line. Let me know what result /error are you getting.

$sql = "SELECT s.name,COUNT(a.id),COUNT(r.id) FROM product_items AS i 
LEFT JOIN watch_addtocart AS a ON a.id = i.id 
LEFT JOIN watch_removefromcart AS r ON r.id = i.id 
LEFT JOIN product_styles AS s ON s.id = i.product_style_id 
GROUP BY [b]s.name [/b]";

[lifeworks]

I am an iiiidiot

$sql = "SELECT s.name,COUNT(a.id),COUNT(r.id) FROM product_items AS i 
				LEFT JOIN watch_addtocart AS a ON a.itemid = i.id 
				LEFT JOIN watch_removefromcart AS r ON r.itemid = i.id 
				LEFT JOIN product_styles AS s ON s.id = i.product_style_id 
				GROUP BY i.product_style_id";

i had the addto, removefrom table joined onto the item table using their id, not their itemid - their id is just an auto_increment

all working now :)