MySQL JOIN UNION Result

Question

Is it possible to associate the UNION result of two tables with a third table? Something like that:

 (SELECT DISTINCT `Fund_ID`, `Fund_Name` FROM `admin` UNION SELECT `Fund_ID`,`Fund_Name` FROM `admin_custom` WHERE `admin_custom`.`user_id` = 361) a LEFT JOIN `qt1` ON `qt1`.`Fund ID` = a.`Fund_ID` 

but this code does not work. I could move the JOIN inside each SELECT query to UNION , but would rather try a JOIN with the result of UNION .

How can i fix this?

Answer 1

Yes it is possible. However, your code is incorrect because you are missing the SELECT because your first choice becomes a set of rows (created at runtime). That is, you must specify the SELECT and the fields that you want to receive. The simplest case:

 SELECT a.* FROM (SELECT DISTINCT `Fund_ID`, `Fund_Name` FROM `admin` UNION SELECT `Fund_ID`,`Fund_Name` FROM `admin_custom` WHERE `admin_custom`.`user_id` = 361) AS a LEFT JOIN `qt1` ON `qt1`.`Fund ID` = a.`Fund_ID` 

Answer 2

 SELECT * FROM (SELECT DISTINCT `Fund_ID`, `Fund_Name` FROM `admin` UNION SELECT `Fund_ID`,`Fund_Name` FROM `admin_custom` WHERE `admin_custom`.`user_id` = 361) a LEFT JOIN `qt1` ON `qt1`.`Fund ID` = a.`Fund_ID`;