Product
id | name | description
1 | one | test
2 | two | test
Joined Table
id | product_id | is_profile | image
1 | 1 | 0 | 1.jpg
2 | 1 | 0 | 2.jpg
3 | 2 | 0 | 3.jpg
4 | 2 | 1 | 4.jpg
How to find a single image field from joined table if is_profile!=1 then any data else is_profile=1 particular data with is_profile=1? I am using join query with product table. query:
SELECT Product.*,
(SELECT joined_table.image
FROM joined_table
WHERE joined_table.product_id=Product.id
AND joined_table.is_profile=IF(joined_table.is_profile = '1', 1, 0) LIMIT 1) product_image
FROM products AS Product
You can check
select p.id, p.name, p.description,(select case when
(select jt.image from joined_table jt where
jt.product_id=p.id and jt.is_profile=1 limit 1)
is not null
then (select jt.image from joined_table jt where
jt.product_id=p.id and jt.is_profile=1 limit 1)
else (select jt.image from joined_table jt where
jt.product_id=p.id and jt.is_profile=0 limit 1)
end) as img
from Product p where p.id=2 limit 1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments