I have two MySQL tables:
How to select all product with sale='1', order by update_time, the lowest price per product (store in child_price in tbl_child_product table) with one query?
Thank you!
May be UNION clause will help you.
(SELECT * from tbl_product where product_id = 'NULL')
UNION
(SELECT * from tbl_child_product where child_id = 'NUll')
and so on for all values .
You can use inner join of both the tables as:
Select p.product_id, p.product_name, min(c.child_price), p.update_time
from tbl_product p inner join tbl_child_product c
on p.product_id = c.product_id
group by p.product_id
having sale='1'
order by p.update_time
Updated the answer as per question: Use having sale='1' to filter the results of group by
You could join between tbl_product and an aggregate query on tbl_child_product:
SELECT p.product_id, p.product_name, c.lowest_price
FROM tbl_product p
JOIN (SELECT product_id, MIN(child_price) lowest_price
FROM tbl_child_product
GROUP BY product_id) c ON p.product_id = c.product_id
ORDER BY p.update_time
