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

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.