MYSQL Inner Join two table over two keys

Question

I am doing a query to return all users shopping carts, stored in the sb_carts table. The product information stored in sb_carts is referenced over two keys product_sku and school_id. It needs to reference both to return a unique product with unique stock levels etc.

When I execute the following query it returns one row, I am expecting 3 rows. I have tried breaking the inner join into two separate joins but this still returns only 1 result. joining only on one key has the desired result, but may be retuning the wrong product. A left join returns 3 rows but some data is missing product specific

Here is a simplified example of what I am doing

SELECT sb_carts.product_sku
FROM sb_carts 
INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku 
AND sb_products.school_id = sb_carts.school_id
WHERE sb_carts.order_id = 0 
AND sb_carts.user_id = 2 
GROUP BY sb_carts.cart_id

The Full Query looks like

SELECT COUNT(DISTINCT sb_carts.cart_id) as quantity, 
sb_carts.* FROM sb_carts 
INNER JOIN sb_children ON sb_children.child_id = sb_carts.child_id 
INNER JOIN sb_school_entities ON sb_school_entities.school_id   = sb_children.school_id 
INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku 
AND sb_products.school_id = sb_carts.school_id LEFT JOIN sb_houses ON sb_children.house_id = sb_houses.id 
LEFT JOIN sb_refund_cart ON sb_carts.cart_id = sb_refund_cart.cart_id 
WHERE sb_carts.order_id = 0 
AND sb_carts.user_id = 2 
GROUP BY sb_carts.child_id, sb_carts.product_sku, sb_carts.school_id 
ORDER BY sb_children.dob_year, sb_children.dob_month, sb_children.dob_day ASC

Answer 1

The GROUP BY statement is used in conjunction with the aggregate functions. I do not see your aggregate function? Do you really need the group by?

Answer 2

The problem is most likely GROUP BY. This will return only one record per cart ID, even if there are multiple products in the cart. To get what you want, try this:

SELECT sb_carts.cart_id, sb_carts.product_sku
FROM sb_carts 
INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku 
AND sb_products.school_id = sb_carts.school_id
WHERE sb_carts.order_id = 0 
AND sb_carts.user_id = 2 
ORDER BY sb_carts.cart_id

That will return two columns (the cart ID and SKU) instead of one, and all the items for the one cart ID will appear as consecutive rows in the query.

Answer 3

Why are you using the Group By statement? If you are not using a function in the Select like Sum, AVG, etc makes no sense for your porpouse. So, try running the query without the Group By statement :-)

Hope it helps!

Santi! :-)