Magento Question posted in
The official documentation can be found here.

Magento SQL get product sku with the right active category in multishops

I’m traying to get product sku that are displayed on the website with the category of that product.

Output:

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

I have look at these tables:

catalog_category_product
catalog_product_entity
catalog_category_entity
catalog_category_entity_varchar

The query give a lot of rows back for just one sku i get about 100 records back. i don’t see which one is the right category that is active right now..

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"
Tags:

2

Answers


  1. 0

    Assuming your are using a mysql database
    Try Don’t using quote around object name (table and column name)

    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
    Login or Signup to reply.
  2. 0

    You can try using left join

    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"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search