0

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...?!

2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by lifeworks
0

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]";

Edited by urtrivedi: n/a

0

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 :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.