Multiple WHERE with LIMIT MySQL

Question

Using PHP, is it possible to select multiple rows from one table, in a MySQL database, with different WHERE clauses having a specific LIMIT for each WHERE clause.

For example:

SELECT * FROM the_table WHERE color = 'blue' LIMIT 5 OR color = 'red' LIMIT 10

I know the above statement does not work. But is there a way to do this with a single call to the database?

Answer 1

SELECT * FROM the_table WHERE color = 'blue' LIMIT 5

UNION

SELECT * FROM the_table WHERE color = 'red' LIMIT 10 ;

Answer 2

You can use a UNION, if I understand your post correctly:

(SELECT * FROM the_table WHERE color = 'blue' LIMIT 5)
UNION ALL
(SELECT * FROM the_table WHERE color = 'red' LIMIT 10)

Answer 3

I believe flow control in MySQL is what you're looking for.

Answer 4

Have a look at the official MySQL documentation regarding subqueries (there's a reference made to LIMITs here, and some cases they may not be valid in): http://dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html -- you may find an optimisation using subqueries in there, but UNION is probably quite fast and will probably be auto-optimised.