php - Fetch min() from other table with multiple relations

136

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

Answer

380

Answer

-------- 1 | 1 | 100 | 1 2 | 1 | 150 | 2 3 | 2 | 200 | 2 4 | 2 | 250 | 1 Suppliers id | name

292

Answer

-------- 1 | Supplier1 2 | Supplier2

946

Answer

Solution:

You have to get the lowest price by joining on a subquery grouping the prices and returning lowest price and corresponding product_id.

Then join again on the prices table to get the dataset of the lowest price.

Then use the supplier_id of the lowest price dataset to join the supplier.

Fiddle

SELECT
    products.id,
    lowestPrice.price,
    prices.id AS prices_id,
    products.desc,
    prices.supplier_id,
    suppliers.name
FROM
    products
INNER JOIN (
    SELECT
        prices.price,
        prices.product_id
    FROM
        prices
    GROUP BY
        product_id
    HAVING
        MIN(prices.price)
) AS lowestPrice ON lowestPrice.product_id = products.id
INNER JOIN prices ON prices.price = lowestPrice.price AND prices.product_id = lowestPrice.product_id
INNER JOIN suppliers ON prices.supplier_id = suppliers.id
WHERE 
 MATCH (products.desc) AGAINST ('test*' IN BOOLEAN MODE)

People are also looking for solutions to the problem: PHP: Get first and last element entries in array and it's keys

Source