Product SQL (OMG Magento, really?)

product.sql

SELECT
    -- Attributes in the pivot table:
    -- `p`.`whatever` AS `whatever`
    
    p.entity_id AS id,
    p.sku AS sku,

    -- Attributes in the EAV tables:
    -- IFNULL(`store_whatever`.`value`, `default_whatever`) AS `whatever`
    
    IFNULL(store_name.value, default_name.value) AS name,
    IFNULL(store_description.value, default_description.value) AS description

-- pivot table
FROM catalog_product_entity AS p

-- default 'name'
LEFT JOIN catalog_product_entity_varchar AS default_name
    ON (default_name.entity_id = p.entity_id)
    AND default_name.store_id = 0
    AND default_name.attribute_id = (
        -- This kind of subquery can be skipped if you know
        -- the attribute_id or if you have it previously stored
        -- in a variable (you probably should...)
        SELECT attribute_id
        FROM eav_attribute
        WHERE entity_type_id = 4 AND attribute_code = 'name'
    )

-- store specific 'name'
LEFT JOIN catalog_product_entity_varchar AS store_name
    ON (store_name.entity_id = p.entity_id)
    AND store_name.store_id = (
        -- Again, the subquery can be skipped if the
        -- store_id is known
        SELECT store_id
        FROM core_store
        WHERE code = 'en_us'
    )
    AND store_name.attribute_id = (
        SELECT attribute_id
        FROM eav_attribute
        WHERE entity_type_id = 4 AND attribute_code = 'name'
    )

-- default 'description'
LEFT JOIN catalog_product_entity_varchar AS default_description
    ON (default_description.entity_id = p.entity_id)
    AND default_description.store_id = 0
    AND default_description.attribute_id = (
        SELECT attribute_id
        FROM eav_attribute
        WHERE entity_type_id = 4 AND attribute_code = 'description'
    )

-- store specific 'description'
LEFT JOIN catalog_product_entity_varchar AS store_description
    ON (store_description.entity_id = p.entity_id)
    AND store_description.store_id = (
        SELECT store_id
        FROM core_store
        WHERE code = 'en_us'
    )
    AND store_description.attribute_id = (
        SELECT attribute_id
        FROM eav_attribute
        WHERE entity_type_id = 4 AND attribute_code = 'description'
    )

-- And so on...
-- It's really two joins per attribute that is not in the pivot table!
-- Have "fun" with Magento...

-- Put any where conditions, sorting, limit, below.
-- If you want to filter based on the EAV attributes, make sure
-- you reference them using `scope_attribute_name`.`value`.
-- You probably need some 'OR' conditions. For example:
WHERE store_name.value LIKE '%foo%'
    OR default_name.value LIKE '%foo%';