CODEWITHSUNDEEP
mysql
Devaki Arulmami
Devaki Arulmami

Reputation: 131

combine multiple WHERE in single query with LIMIT 2 each

I have column like below

name              |     cat
----------------------------------------
ala               |     games
alaa              |     software
aha               |     games
asd               |     games
aad               |     software
aas               |     software
asd               |     books
aad               |     software
aas               |     books

So i have three query like below

SELECT * FROM `table` WHERE MATCH(`name`) AGAINST ('a*' IN BOOLEAN MODE) AND cat='games' LIMIT 2 
SELECT * FROM `table` WHERE MATCH(`name`) AGAINST ('a*' IN BOOLEAN MODE) AND cat='software' LIMIT 2
SELECT * FROM `table` WHERE MATCH(`name`) AGAINST ('a*' IN BOOLEAN MODE) AND cat='books' LIMIT 2

So i want get first 2 result from each category (games,books,software) ...Right now i use these three queries..how to combine them into ONE QUERY..

P.s This is just example..I am working with upto 10 categories ..I want something which dont waste much resource

Upvotes: 1

Views: 45

Answers (1)

Sablefoste
Sablefoste

Reputation: 4192

Assuming these queries work, use UNION:

SELECT DISTINCT * FROM `table` WHERE MATCH(`name`) 
   AGAINST ('a*' IN BOOLEAN MODE) LIMIT 2 WHERE cat='games'
UNION
SELECT DISTINCT * FROM `table` WHERE MATCH(`name`) 
   AGAINST ('a*' IN BOOLEAN MODE) LIMIT 2 WHERE cat='books'
UNION 
SELECT DISTINCT * FROM `table` WHERE MATCH(`name`) 
   AGAINST ('a*' IN BOOLEAN MODE) LIMIT 2 WHERE cat='software'

Regarding optimization, the documentation states "When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows." So you should add this to limit the resources of the individual queries.

Upvotes: 1

Related Questions