3
SELECT entity_id, sku , type_id
FROM catalog_product_entity
WHERE entity_id NOT IN (
    SELECT product_id
    FROM `sales_flat_order_item` 
)
ORDER BY sku

Need to include product name with above SQL Query and unsure how to format the join.
Magento 1.9.3.2

  • Welcome to StackExchange, Brian. Are you trying to get products which are unsold in your store? – Jaimin Sutariya Aug 17 '17 at 11:51
  • Yes. That is exactly what I am trying to do. – Brian Urso Aug 17 '17 at 11:51
  • Is there any specific reason for using query? I think you should try Magento's collection and joins to get data instead of using raw sql query. – Jaimin Sutariya Aug 17 '17 at 11:55
  • Using the query method so that I can use it as a live data feed for a KPI dashboard. Klipfolio.com – Brian Urso Aug 17 '17 at 11:56
2

A little complicated query will be like below. It will also support store views (If you have different name for the product in different stores).

SELECT e.entity_id, e.sku , e.type_id, 
IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`
FROM catalog_product_entity  as e
INNER JOIN 
         `catalog_product_entity_varchar` AS `at_name_default` 
               ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND 
                  `at_name_default`.`store_id` = 0
LEFT JOIN 
          `catalog_product_entity_varchar` AS `at_name` 
               ON (`at_name`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND 
                  (`at_name`.`store_id` = 1)                   
WHERE e.entity_id NOT IN (
    SELECT product_id
    FROM `sales_flat_order_item` 
)
ORDER BY e.sku

You should not use entity_type_id as static value, instead you can use Magento's identifier catalog_product Reference

  • Thanks! This works as well, appreciate the additional capability. – Brian Urso Aug 17 '17 at 12:54
2

This should do it:

SELECT e.entity_id, e.sku, e.type_id, v.value as name
FROM catalog_product_entity as e
INNER JOIN catalog_product_entity_varchar as v 
    ON v.entity_id = e.entity_id 
    AND v.attribute_id IN (SELECT attribute_id FROM eav_attribute where attribute_code = 'name' and entity_type_id = 4)
WHERE e.entity_id NOT IN (
    SELECT product_id
    FROM `sales_flat_order_item` 
)
ORDER BY sku

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.