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   
37

Answer

1 | product_1 2 | product_2 Prices id | product_id | price | supplier_id
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

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.