0

Hi i want to extract list of all these fields with respect to products in my magento2 store from sql query but no idea in which table they are , please provide me necessary sql query for that job , i would be so thankful to you peoples!

Category |  BrandName | SupplierName |  ProductName | Barcode
0

I hope my answer would help many beginners of magento2.

get all brand names

SELECT attribute_code , EAOV.VALUE
FROM EAV_ATTRIBUTE EA
LEFT JOIN EAV_ATTRIBUTE_OPTION EAO ON EAO.ATTRIBUTE_ID = EA.ATTRIBUTE_ID
LEFT JOIN EAV_ATTRIBUTE_OPTION_VALUE EAOV ON EAOV.OPTION_ID = EAO.OPTION_ID
WHERE EA.ATTRIBUTE_ID = 174
AND EAOV.STORE_ID = 0

and for Get all products with respective category

Method 1 :

SELECT pr.entity_id AS product_id
     , pr.name AS product_name
     , catn.name AS category_name 
  FROM catalog_product_flat_19 AS pr 
  LEFT 
  JOIN catalog_category_product AS cat 
    ON pr.entity_id = cat.product_id 
  LEFT 
  JOIN catalog_category_flat_store_19 AS catn 
    ON catn.entity_id = cat.category_id 
 ORDER BY pr.entity_id ASC;

Method 2 :

SELECT DISTINCT pr.entity_id AS entity_id
     ,pr.sku as sku 
     ,B.value as Barcode
     , pr.name AS product_name
     , catn.name AS category_name 
  FROM catalog_product_flat_19 AS pr 
  LEFT 
  JOIN catalog_category_product AS cat 
    ON pr.entity_id = cat.product_id 
  LEFT 
  JOIN catalog_category_flat_store_19 AS catn 
    ON catn.entity_id = cat.category_id 
  inner join catalog_product_entity_varchar as B on pr.entity_id = B.entity_id 

where B.attribute_id = 172 ;

and for Total number of categories

SELECT DISTINCT cc.entity_id AS id, cc.value AS path, cc1.value AS NAME    
FROM 
    catalog_category_entity_varchar cc    
    JOIN catalog_category_entity_varchar cc1 ON cc.entity_id=cc1.entity_id    
    JOIN catalog_category_entity cce ON cc.entity_id=cce.entity_id
 WHERE 
    cc.attribute_id = '45' AND 
    cc1.attribute_id = '45' ; 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.