How to get product name and image name from database using query?

Question

I want to get product name and it's image url from the database in single query. I am using following query. I am able to get product name using attribute_id 73 but i have problem to fetch image name using attribute_id 88.

Query

select product.value as name from catalog_product_entity_varchar as product join catalog_category_product as category on product.entity_id=category.product_id where product.store_id=0 and product.attribute_id=73 and product.attribute_id=88 and category.category_id=3

• LucScu: which kind of problem?
• Nitin Pawar: I want to get the image name from same table with arrtibute_id 88. If i add and attribute_id=88 in where clause it gives empty result.
• LucScu: i tried it in my db and your query retrieves correctly image attribute column value, i.e. /e/t/eton1014-4040-00480-26_01.jpg
• Nitin Pawar: I have updated the question can you please check it again, and write answer query for return product name and image name from DB.

Answer 1

If you want to retrieve product name and product image togheter you have to use this query

select category.product_id, product1.value as name, product2.value as image
from catalog_category_product as category 
join catalog_product_entity_varchar as product1 on product1.entity_id=category.product_id and product1.attribute_id=60 and product1.store_id=0
join catalog_product_entity_varchar as product2 on product2.entity_id=category.product_id and product2.attribute_id=74 and product2.store_id=0
where category.category_id=3
order by category.product_id asc

Change 60 and 74 with your attribute id 73 and 88.

• LucScu: Your welcome, please accept if it works ;)