I have 3 tables and would like to select data from table1&table2, then from table3&table2, and finally, concatenate both queries and get the last 10 elements of the result.
Each of these queries works fine. The problem occurs when I use UNION

SELECT t1.postID, 
       t1.status,
       t1.`number`, 
       t2.reference, 
       t2.joint_date
FROM table1 t1 
INNER JOIN table2 t2  ON t1.postID=t2.postID
WHERE t1.active=1 
AND t1.userID=3
ORDER BY t1.postID ASC
UNION
SELECT t3.postID, 
       t3.status,
       t3.`number`, 
       t4.reference, 
       t4.joint_date
FROM table3 t3
INNER JOIN table2 t4 ON t3.postID=t4.postID
WHERE t3.active=1 
AND t3.userID=3
ORDER BY t3.postID ASC
LIMIT 0, 5;

I am just getting an error. How could I achieve this with one query ?

CodePudding user response：

When combining UNION and ORDER BY and LIMIT in a single query, it is important to recognise that ORDER BY and LIMIT will apply to the entire UNIONED result set. For this reason we can only specify ORDER BY and LIMIT after the final query.

The ORDER BY and LIMIT in a query that has been concatenated with a UNION or UNION ALL are not actually part of the last expression, they are actually after it. For this reason you cannot use table aliases in the ORDER BY, instead you can only use the column alias that is defined in the first expression, before the first UNION.

If you want to get the LAST 10 records from your query then we can simply reverse the order of the postID:

SELECT t1.postID, 
       t1.status,
       t1.`number`, 
       t2.reference, 
       t2.joint_date
FROM table1 t1 
INNER JOIN table2 t2  ON t1.postID=t2.postID
WHERE t1.active=1 
AND t1.userID=3
ORDER BY t1.postID ASC

UNION

SELECT t3.postID, 
       t3.status,
       t3.`number`, 
       t4.reference, 
       t4.joint_date
FROM table3 t3
INNER JOIN table2 t4 ON t3.postID=t4.postID
WHERE t3.active=1 
AND t3.userID=3

ORDER BY postID DESC
LIMIT 0, 10;

Notice that I have deliberately injected a space between the last expression and the ORDER BY, this is to highlight visually that the ORDER BY and LIMIT in this query are part of the UNION and not part of the second query. Notice also that we do need (and cannot use) the table alias to reference the postID column.

CodePudding user response：

Place both halves of the union into separate tuples:

(SELECT t1.postID, t1.status, t1.`number`, t2.reference, t2.joint_date
 FROM table1 t1
 INNER JOIN table2 t2 ON t1.postID = t2.postID
 WHERE t1.active = 1 AND t1.userID = 3)
UNION
(SELECT t3.postID, t3.status, t3.`number`, t4.reference, t4.joint_date
 FROM table3 t3
 INNER JOIN table2 t4 ON t3.postID = t4.postID
 WHERE t3.active = 1 AND t3.userID = 3
 ORDER BY t3.postID
 LIMIT 0, 5)
ORDER BY postID;

Note that the final ORDER BY clause applies to the entire result set after the union has finished.