MySQL: Collect records from multiple queries into one result

Question

I am looking to create a SQL query that collects the results from multiple queries.

I would like to apply a random sort order and return a limited number of the records.

The queries will all return the same field ("RefCode" in this example), but will have different FROM and WHERE statements.

See an example below of some possible queries:

SELECT PD.RefCode
FROM ((PD 
INNER JOIN P ON PD.PrdFK = P.PrdID) 
INNER JOIN PR ON PR.ChildCatFK = P.ChildCatFK)
WHERE PR.ParentCatFK = 6

SELECT PD.RefCode
FROM (PR 
INNER JOIN PD ON PR.PrdDetFK = PD.PrdDetID)
WHERE PR.ChildCatFK = 14

I have tried doing this using various techniques (i.e. UNION) but have not been able to get the right method.

The random sorting and limiting of records (Using RAND() and LIMIT 0,10) is not essential but it would be great to have.

Is this possible or do I need to create each query separately?

Answer 1

Use a UNION to combine the queries. Wrap them in a subquery so you can ORDER BY and LIMIT the result:

SELECT RefCode
FROM (
    SELECT PD.RefCode
    FROM PD 
    INNER JOIN P ON PD.PrdFK = P.PrdID
    INNER JOIN PR ON PR.ChildCatFK = P.ChildCatFK
    WHERE PR.ParentCatFK = 6

    UNION ALL

    SELECT PD.RefCode
    FROM PR 
    INNER JOIN PD ON PR.PrdDetFK = PD.PrdDetID
    WHERE PR.ChildCatFK = 14

) subquery
ORDER BY RAND() 
LIMIT 10