I have a Jobs and a Companies table, and I want to extract 20 jobs that meet the following criteria:
- Jobs only from two (2) named companies
- There can at most be 10 jobs per company
I have tried the following
UNION DISTINCT, but the problem is that the
LIMIT 0,10 applies to the whole result set. 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.
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);