MYSQL内部连接两个键超过两个表 [英] MYSQL Inner Join two table over two keys

问题描述

我正在执行查询以返回存储在sb_carts表中的所有用户购物车. sb_carts中存储的产品信息通过两个键product_sku和school_id进行引用.它需要同时引用两者，以退回具有独特库存水平的独特产品等.

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.

当我执行以下查询时，它返回一行，我期望有3行. 我尝试将内部联接分成两个单独的联接，但这仍然只返回1个结果.仅使用一个键进行连接即可获得所需的结果，但可能会重新调整错误的产品.左联接返回3行，但某些数据缺少特定于产品的

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

完整查询如下

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

推荐答案

为什么要使用Group By语句?如果您未在Select中使用诸如Sum，AVG等的函数，则对您的海豚没有意义. 因此，尝试在没有Group By语句的情况下运行查询:-)

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

希望有帮助！

桑蒂！ :-)

这篇关于MYSQL内部连接两个键超过两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！