CODEWITHSUNDEEP
mysqlsqlfull-text-search
jack brone
jack brone

Reputation: 205

Mysql Fulltext Search with Left Join Another Table

I am preparing a MySQL fulltext search application.

I have two tables: Books and Price. I need to get data from both tables when search perform.

Books schema:

id,name,title,active
1,Alex, Alex Bio,1
2,Bob, Bob Bio, 1

mysql fulltext index(name,title)

Price schema:

id,product_id,price
1,1,500
2,1,600
3,1,700
4,2,300
5,2,400
6,2,500

When I run the following query, search works fine but I need price; and only the lowest price should be displayed along with product_id.

The following is my full query:

SELECT *,
  MATCH (name, title)
    AGAINST ('" . $search . "' IN BOOLEAN MODE) AS "high"
  FROM books
  WHERE MATCH (name, title) AGAINST ('" . $search . "' IN BOOLEAN MODE)
    AND active = 1
  ORDER BY high DESC;

Expected Output:

id,name,title,price
1,Alex,Alex Bio,500
2,Bod,Bob Bio,300

Upvotes: 2

Views: 937

Answers (2)

Shadow
Shadow

Reputation: 34243

Just join the price table to books, get the minimum price and add group by clause:

SELECT books.id, name, title, min(p.price)
FROM books
INNER JOIN price p on p.product_id=books.id
WHERE MATCH (name,title) AGAINST ('" . $search . "' IN BOOLEAN MODE) AND active = 1
GROUP BY books.id, name, title
ORDER BY MATCH (name,title) AGAINST ('" . $search . "' IN BOOLEAN MODE) DESC

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

You need a join to make this work:

SELECT t1.id, t1.name, t1.title, COALESCE(t2.minPrice, 'NA') AS price
FROM books t1
LEFT JOIN
(
    SELECT product_id, MIN(price) AS minPrice
    FROM price
    GROUP BY product_id
) t2
    ON t1.id = t2.product_id
WHERE MATCH (name,title) AGAINST ('" . $search . "' IN BOOLEAN MODE) AND active = 1
ORDER BY MATCH (name,title) AGAINST ('" . $search . "' IN BOOLEAN MODE) DESC

Upvotes: 2

Related Questions