MYSQL Inner Join two table over two keys
- by bertsisterwanda
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
