Line in Linus Line in Linus - 4 months ago 24
MySQL Question

Min() with INNER JOIN and Full-text-search

So Ive got the following Query to work just fine, searching for '

test
*' in '
products_desc
' column and fetching all its prices in the '
prices
' table.

SELECT products.id, prices.price, products.product_desc FROM products
INNER JOIN prices
ON prices.product_id = products.id
WHERE
MATCH (products.product_desc) AGAINST ('test*' IN BOOLEAN MODE)


Although the 'prices' table consists of multiple prices per product and I only want to fetch the lowest one to each product. I've previously filtered the prices using

INNER JOIN (
SELECT min(price) as price, prices.product_number as product_number FROM prices
WHERE prices.product_number LIKE'".$q."%'
GROUP BY prices.product_number
) min_prices
on prices.price = min_prices.price
and prices.product_number = min_prices.product_number


but this was when I used products_numbers within the
prices
table (now there is just a product_id-column.

Products
id | product_desc
----------------------------------------
1 | product1
2 | product2

Prices
id | product_id | price
------------------------------------------------
1 | 1 | 312
2 | 1 | 219
3 | 2 | 312
4 | 2 | 111


Also, the table consists of 10+ million rows so, efficiency matters a lot :)

EDIT

What if I need to access for columns on the min(prices.price) row?

SELECT products.id, MIN(prices.price) as prices_price, prices.id as prices_id, products.product_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.product_desc) AGAINST ('test*' IN BOOLEAN MODE)
GROUP BY prices.product_id


The above returns the lowest price per product but also the wrong value in the other columns?

Answer

You can use GROUP BY for this, with MIN() group by function.

GROUP BY is used to group values from a column, and perform calculations on column.

In our case we want to group the result by product_id as it's repeating in second table and perform calculation (min()) on price column of second table.

This is how your Query would look like:

SELECT products.id, MIN(prices.price), products.product_desc FROM products
INNER JOIN prices
ON prices.product_id = products.id
WHERE 
MATCH (products.product_desc) AGAINST ('test*' IN BOOLEAN MODE)
GROUP BY prices.product_id
Comments