Mysql – UNION SELECT after LEFT JOIN and JOIN

MySQL

    SELECT DISTINCT re.*
    FROM `reports` r
    LEFT JOIN `users` u
    ON u.`id_rep` = r.`id`
    JOIN `customers` c
    ON u.`id` = r.`uid`
    WHERE r.`uid` = '1' and r.`name` LIKE 'urgent'
    ORDER BY r.date DESC

Without making any modifications prior to the WHERE clause, would it be possible to use UNION (or a better approach) after the LIKE statement in order to SELECT from a fourth table called workers and ORDER the results BY r.date DESC?

Best Answer

Using Union and ORDER BY:

Be explicit with results form first query:

(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')

UNION
(SELECT username,rdate FROM users)


ORDER BY rdate DESC

I assumed the second table had a date column. Otherwise and explicit value could be used.

Related Question