How Can I Join Two DB Tables and Return Lowest Price From Joined Table
- by Jason
I have two tables, the first table has the product and the second table the prices. The price table could have more than one price per product but I only want to display the lowest. But I keep getting all the prices returned and I'm having trouble figuring out how to do it.
this is what I get returned in my query:
SELECT * FROM products AS pr JOIN prices AS p ON pr.id = p.product_id WHERE pr.live = 1 AND p.live = 1
id product1 name description £100
id product1 name description £300
id product1 name description £200
id product2 name description £50
id product2 name description £80
id product2 name description £60
id product3 name description £222
id product3 name description £234
id product3 name description £235
but I'm after:
id product1 name description £100
id product2 name description £50
id product3 name description £222
Any help would be appreciated
