How to get product details and its category from sql query?

[vatsalshah74a9]

Please help to get product details and its corresponding categories using sql query

[Manish Mittal]

Hello @vatsalshah74a9 

Can you try with below query if that will help you:

select cpe.entity_id,
 cpe.sku,
 cpev.value as name,
    cpr.`parent_id`,
   cpe1.sku as parent_sku,
  cpet1.value as short_description, 
  cpev2.value as url_key, 
  cpev3.value as brand,
   cpei.value as status,
   cpei1.value as visibility,
   (select class_name from tax_class where class_id = cpei2.value and class_type='PRODUCT') as tax_class, 
   (select value from eav_attribute_option_value eaov where option_id=cpei3.value and store_id=0) as include_in_feed,
   cped.value as price,
   cped1.value as special_price,
   mg.value as media_gallery,
   cids.category_ids,
   cids.category_names,
   cids.category_paths
   from catalog_product_entity cpe 
left join catalog_product_entity_varchar as cpev on cpev.entity_id=cpe.entity_id and cpev.attribute_id=73 -- name --
left join catalog_product_entity_text as cpet1 on cpet1.entity_id=cpe.entity_id and cpet1.attribute_id=76 -- short description --
left join catalog_product_entity_varchar as cpev2 on cpev2.entity_id=cpe.entity_id and cpev2.attribute_id=119 -- url key --
left join catalog_product_entity_varchar as cpev3 on cpev3.entity_id=cpe.entity_id and cpev3.attribute_id=136 -- brand --
left join catalog_product_entity_int as cpei on cpei.entity_id=cpe.entity_id and cpei.attribute_id=97 -- status --
left join catalog_product_entity_int as cpei1 on cpei1.entity_id=cpe.entity_id and cpei1.attribute_id=99 -- visibility --
left join catalog_product_entity_int as cpei2 on cpei2.entity_id=cpe.entity_id and cpei2.attribute_id=134 -- tax class --
left join catalog_product_entity_int as cpei3 on cpei3.entity_id=cpe.entity_id and cpei3.attribute_id=184 -- include in feed --
left join catalog_product_entity_decimal as cped on cped.entity_id=cpe.entity_id and cped.attribute_id=77 -- price --
left join catalog_product_entity_decimal as cped1 on cped1.entity_id=cpe.entity_id and cped1.attribute_id=78 -- specials price --
left join catalog_product_relation cpr on cpr.child_id=cpe.entity_id left join catalog_product_entity cpe1 on cpe1.entity_id=cpr.parent_id
left join cataloginventory_stock_status css on cpe.entity_id=css.product_id 
LEFT JOIN 
  (SELECT product_id,
          GROUP_CONCAT(distinct c.category_id SEPARATOR ',') AS category_ids,
          GROUP_CONCAT(distinct cv.value  SEPARATOR ',') AS category_names,
          GROUP_CONCAT(distinct cv1.value SEPARATOR ',') AS category_paths
   FROM catalog_category_product c
   INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.entity_id and cv.attribute_id=45 and cv.store_id=0
   INNER JOIN catalog_category_entity_varchar cv1 ON c.category_id = cv1.entity_id and cv1.attribute_id=118 and cv.store_id=0
   GROUP BY product_id) cids ON cpe.entity_id = cids.product_id
LEFT JOIN 
  (SELECT m1.entity_id,
          GROUP_CONCAT(m2.value) AS value
   FROM catalog_product_entity_media_gallery_value_to_entity m1
   INNER JOIN catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id and m2.`attribute_id`=90
   GROUP BY m1.entity_id) mg ON cpe.entity_id=mg.entity_id
where cpe.type_id='simple' and css.stock_status=1 and css.qty>0 limit 1000 offset 10;

You can change where condition based on your requirement. Let me know if anything specific you needed. Thanks

[vatsalshah74a9]

The output is coming NULL for all the column.Could you please help what could be issue.

specifically what i want is to create table similar to flat_store_1 where i can have all the products.

Flat store 1 has limited tables.

[Bhanu Periwal]

Hi @vatsalshah74a9 

kindly refer below links : 

https://magento.stackexchange.com/questions/128269/sql-list-all-categories-and-products-skus-in-cate...

https://gist.github.com/tegansnyder/7828065

If issue resolve, Please click on 'Kudos' & Accept as Solution!

[vatsalshah74a9]

@Bhanu Periwal It worked but categories didn't came as Main category,child category,child child category...

The sequence didn't come right Any help in that would be great.

[Bhanu Periwal]

Hi @vatsalshah74a9 

Please try this query :

SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM (
SELECT `e`.entity_id, `at_category_id`.`category_id` 
FROM `catalog_product_entity` AS `e` 
LEFT JOIN `catalog_category_product` AS `at_category_id`
ON (at_category_id.`product_id`=e.entity_id)
) sub_query
GROUP BY entity_id

This query will show product list with child chain. 

Query products in multiple categories (each product has a default category, then the child cat)

SELECT entity_id, category_ids, (LENGTH(category_ids) - LENGTH(REPLACE(category_ids, ',', '')) + 1) as category_cnt FROM ( SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM ( SELECT `e`.entity_id, `at_category_id`.`category_id` FROM `catalog_product_entity` AS `e` LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=e.entity_id) ) sub_query GROUP BY entity_id ) final_query HAVING category_cnt > 2

If issue resolve, Please click on 'Kudos' & Accept as Solution!