MySQL limit with a where clause

Question

I have some data in a view that I want to search. The data has different types (defined in a column), and I want to define a different limit for each type.

Example my_view

id   |    type    |     content
--        ----          -------
1         'dog'        'Lorem ipsum dolor sit amet, consectetur...'
2         'cat'        'adipisicing elit, sed do eiusmod...'
3         'cat'        'tempor incididunt ut labore...'
4         'dog'        'et dolore magna aliqua...'

I want a query along the lines of:

SELECT * FROM `my_view` WHERE ... LIMIT [[ max of 2 dogs, 1 cat and 1 sheep]]

I could of course do three queries for this and fit them together (either in the query or afterwards), but as far as I am aware, the view will be re-created for each search and so it would be quite inefficient.

Answer 1

use sub select like this :

SELECT * 
FROM dogs, cats, sheeps, animals 
WHERE 
dogs.dog_id = (select dogs.dog_id from dogs where dogs.fk_animal_id = animals.animal_id limit 2) AND 
cats.cat_id = (select cats.cat_id from catswhere cats.fk_animal_id = animals.animal_id limit 1) AND 
sheeps.sheep_id = (select sheeps.sheep_id from sheeps where sheeps.fk_animal_id = animals.animal_id limit 1) 

Answer 2

You can't do all in one.

Use Union

(SELECT * 
FROM my_view
WHERE type='dog'
ORDER BY yyy
LIMIT 2)

UNION

(SELECT * 
FROM my_view
WHERE type='cat'
ORDER BY xxx
LIMIT 1)

etc.