Magento SQL在多跳中获得具有正确活动类别的产品sku

Question

我正在努力获取与该产品类别一起显示在网站上的产品sku。

输出：

+-------------+------------+
| sku         | category_n |
+-------------+------------+
|      855202 |      test1 |
|     87972_k |      test2 |
|      887997 |      test1 |
+-------------+------------+

我看过这些表格：

catalog_category_product
catalog_product_entity
catalog_category_entity
catalog_category_entity_varchar

查询只为一个sku返回了很多行，我返回了大约100条记录。我不知道哪一个是目前活跃的正确类别。。

SELECT
* 
FROM
"catalog_category_product" as ccp 
JOIN
"catalog_product_entity" as cpe ON "cpe.entity_id" = "ccp.product_id"
JOIN  
"catalog_category_entity" as cat ON "cat.entity_id" = "ccp.category_id"
JOIN 
"catalog_category_entity_varchar" as cv on cat.entity_id = cv."entity_id"

Answer 1

假设您使用的是mysql数据库试着不要在对象名称(表和列名(周围使用引号

SELECT * 
FROM catalog_category_product as ccp 
JOIN catalog_product_entity as cpe ON cpe.entity_id = ccp.product_id
JOIN catalog_category_entity as cat ON cat.entity_id = ccp.product_id
JOIN catalog_category_entity_varchar as cv on cat.entity_id = cv.entity_id

Answer 2

您可以尝试使用左联接

SELECT
* 
FROM
"catalog_category_product" as ccp 
Left JOIN
"catalog_product_entity" as cpe ON "cpe.entity_id" = "ccp.product_id"
Left JOIN  
"catalog_category_entity" as cat ON "cat.entity_id" = "ccp.category_id"
Left JOIN 
"catalog_category_entity_varchar" as cv on cat.entity_id = cv."entity_id"