php - Fetch min() from other table with multiple relations
I'm trying to create a full-text-search for products.desc. As response I want
- Products: id, desc
- Prices: The lowest priced-row in relation to products.id
- Suppliers: id, name of the supplier offering that price.
I'm currently using
SELECT products.id, MIN(prices.price) as prices_price, prices.id as prices_id, products.desc, products.product_number, prices.supplier_id, suppliers.name FROM products INNER JOIN prices ON prices.product_id = products.id INNER JOIN suppliers ON prices.supplier_id = suppliers.id WHERE MATCH (products.desc) AGAINST ('test*' IN BOOLEAN MODE) GROUP BY prices.product_id
This is returning the correct (lowest) price, although the value for all the other prices.*-rows is undetermined/wrong. Since GROUP BY prices.product_id fetches the columns/rows with the lowest ID.
Products id | desc