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?

  • I don't understand your EDIT. Please make a stab at the query; maybe it will be clearer then. – Rick James Nov 25 '18 at 23:49

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')

(SELECT username,rdate FROM users)


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

  • There's an example of that in the manual page – danblack Nov 25 '18 at 23:24
  • 1
    Try a new question. You're adding an amount of complexity beyond the initial question and without a full example with sample output clarification by comment is getting increasingly likely to be wrong. – danblack Nov 25 '18 at 23:30

If I understand the question correctly, yes. Each of the following is OK, though not necessarily efficient:

   FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x

   FROM a
   JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...

   FROM a
   LEFT JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...

   FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS a
   JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...

etc. (I may have added more parentheses than necessary.)

More UNION tips:

  • Yes, the number of columns needs to be the same.
  • The default column names come from the first SELECT.
  • If you have a constant string in a column of the first SELECT, that limits the length of the matching string of the other SELECTs. (Simply pad with blanks to 'fix' the problem.)
  • If appropriate, use UNION ALL do avoid the de-duping phase, thereby being faster than UNION, which is the same as UNION DISTINCT.
  • @Rick the last 2 queries are equivalent. The ORDER BY is applied to the whole result set in both of them: dbfiddle.uk/… – ypercubeᵀᴹ Nov 25 '18 at 23:58
  • 1
    @ypercubeᵀᴹ - Geez, I'm only a decade out of step -- bugs.mysql.com/bug.php?id=27848 "fixed" that in 2008 (5.0.56, 5.1.23). Thanks for rattling my cage. – Rick James Nov 26 '18 at 0:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy