How to add product name to SQL Query

Question

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

Answer 1

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

Answer 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