Have Geek Brain

MySQL: collect records from multiple queries into one result

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

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

All queries will return the same field (" RefCode

" in this example), but have different FROM and WHERE clauses.

See example below for 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 tried to do this using various methods (i.e. UNION

) but couldn't get the correct method.

Randomly sorting and limiting entries (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 request separately?

0


a source to share


1 answer


Use UNION to combine queries. Wrap them in a subquery so you can ORDER and RESTRICT 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
+3


a source






More articles:


All Articles