CODEWITHSUNDEEP
mysqlsqlunion-all
Prashant
Prashant

Reputation: 23

MYSQL Union All not returning all record by two merging query

SELECT tah.project_id_new as "project_id", tah.history_createdon from task_audit_history tah 
where task_id=64 and project_id != project_id_new;

Result

SELECT tah.project_id as "project_id", tah.history_createdon from task_audit_history tah 
where task_id=64 ORDER BY history_createdon asc limit 1;

Result

When I tried to combined above two queries using UNION ALL it return only one record. Why ? i mean it 
should include all record from two query.

SELECT tah.project_id_new as "project_id", tah.history_createdon from task_audit_history tah 
where task_id=64 and project_id != project_id_new UNION ALL SELECT tah.project_id as "project_id", tah.history_createdon from task_audit_history tah 
where task_id=64 ORDER BY history_createdon asc limit 1;

Query result

Upvotes: 0

Views: 449

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Your result implies that the ORDER BY and LIMIT clauses are being applied to the entire union query, not just the subquery (which is the behavior you want). Try using this version:

(SELECT tah.project_id_new AS project_id, tah.history_createdon
 FROM task_audit_history tah 
 WHERE task_id = 64 AND project_id != project_id_new)
UNION ALL
(SELECT tah.project_id AS project_id, tah.history_createdon
 FROM task_audit_history tah 
 WHERE task_id = 64
 ORDER BY history_createdon
 LIMIT 1);

For completeness, the following query is what you were likely originally running to end up with just a single row:

SELECT tah.project_id_new AS project_id, tah.history_createdon
FROM task_audit_history tah 
WHERE task_id = 64 AND project_id != project_id_new
UNION ALL
SELECT tah.project_id AS project_id, tah.history_createdon
FROM task_audit_history tah 
WHERE task_id = 64
ORDER BY history_createdon
LIMIT 1;

In this case, the ORDER BY and LIMIT clause applies to the entire union query, which means it will return just a single record.

Upvotes: 3

Related Questions