Supplier,Brand,Category Name fetch of all products from sql ? magento2

Question

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

Answer 1

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' ;