Search code examples
mysqlinner-join

Inner Join apply condition to only one table

Here is my query:

SELECT p.product_id,p.title,p.price,p.total_sales,p.total_revenue,p.timestamp,i.image_id
FROM products AS p
INNER JOIN products_images AS i
ON p.product_id = i.product_id
WHERE p.account_id = ? AND p.deleted=0 AND i.featured=1

I want to get i.image_id WHERE i.featured = 1 but I don't know how to apply that condition so that it only applies to the images table. The query works but I don't get all my results from the products table, just one row.

Obviously in some cases there might not be an image_id available where featured=1. So it would have to spit out something else in that case.

Solution

  • Change it to

    FROM products AS p
left outer join products_images AS i
ON p.product_id = i.product_id
AND i.featured=1
WHERE p.account_id = ? AND p.deleted=0"))