MySQL Combining UNION and LIMIT Operations

Question

Hi,

I have a JOBS and a COMPANIES table, and I want to extract 20 jobs that meet the following criteria
a) jobs only from 2 named companies
b) there can at most be 10 jobs per company

I have tried the following SELECT with UNION DISTINCT, but the problem is that the LIMIT 0,10 applies to the whole result set, whereas I want it to apply to each of the companies. If there aren't 10 jobs per company, then the query should return all the jobs it finds.

SELECT c.name, j.title, j.`desc`, j.link 
  FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name IN ('Company1')
UNION DISTINCT
SELECT c.name, j.title, j.`desc`, j.link 
  FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name IN ('Company2')
ORDER by name, title
LIMIT 0,10

I am new to MySQL, so realise there may be a smarter way to do this instead of with UNION, so any suggestions for improvements are definitely welcome.

Thanks for your help

Mauro Ciaccio

Answer 1

Quoting the docs,

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);