MySQL - SELECT equal amount of each in UNION by total LIMIT

Question

In a MySQL database I have 3 tables: customers, projects and tasks. For a search that I am implementing, I want to search through all 3 tables and select found matches. The problem is that I would like to equally limit the amount of results returned by MySQL.

This is an example with the query that I currently have:

SELECT id, title, type
FROM (
    (
    SELECT id, title, 'customer' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM customers
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (
    SELECT id, title, 'project' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM projects
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (
    SELECT id, title, 'task' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM tasks
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
) res
LIMIT 6;

In this example, I want to limit the results to 6.

My desired endresult for this example is the following:

1) If all tables have at least 2 results, show 2 results for each.

id     title                      type
20     'First test customer'      'customer'
22     'Test customer 2'          'customer
48     'A project for testing'    'project'
17     'Test Project'             'project'
1      'Task test'                'task'
2      'Second test'              'task'

2) If one table does not have any results, show 3 results for each of the 2 other tables. (If only one table has results, show 6 results for that table.)

id     title                      type
20     'First test customer'      'customer'
22     'Test customer 2'          'customer
56     'Customer test 56'         'customer'
1      'Task test'                'task'
2      'Second test'              'task'
3      'Test task'                'task'

3) If 2 of the tables have more than 2 results, and the third table only has 1 result, show 3 results for one of the tables with enough results, 2 results for the other one of the two and 1 for the table with only 1 result.

id     title                      type
20     'First test customer'      'customer'
48     'A project for testing'    'project'
17     'Test Project'             'project'
34     'Testing project'          'project'
1      'Task test'                'task'
2      'Second test'              'task'

Can anyone please help me with this?

Thanks in advance!

Answer 1

You might use a row number for every single SELECT and then order the UNION with that calculated field to balance the results obtained from the single queries (I've not tested this code, please take it as a starting point):

SET @rank1=0;
SET @rank2=0;
SET @rank3=0;
SELECT id, title, type, rank
FROM (
    (

    SELECT @rank1:=@rank1+1 AS rank, id, title, 'customer' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM customers
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (

    SELECT @rank2:=@rank2+1 AS rank, id, title, 'project' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM projects
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (

    SELECT @rank3:=@rank3+1 AS rank, id, title, 'task' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM tasks
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
) res
ORDER BY rank 
LIMIT 6;