I have two tables one called products the other product_images. product_images has 6 fields in this layout.
product_id, small_image, medium_image, large_image, width, height
small_image, medium_image and large_image are null fields.
My data has now duplicated after adding extra fields like small_image and large_image that have the same product_id.
My sql statement
"SELECT *,
i.medium_image, i.width, i.height,
COALESCE((SELECT COUNT(*)
FROM order_details od
WHERE od.product_id = p.product_id), 0) as most_popular
FROM products p
INNER JOIN product_images i on i.product_id = p.product_id
WHERE p.department_id=:department_id AND p.is_active=1
$orderby
LIMIT :limit OFFSET :offset");
What do I need to do change my sql statement to only select the medium_image for the product so it won't duplicate, I tried using DISTINCT but that's not working. I just want to return the single product_image row that is medium_image related to the product_id
