CODEWITHSUNDEEP
mysqlsqljoinunion
user2723490
user2723490

Reputation: 2100

MySQL JOIN the result of UNION

Is it possible to join the result of UNION of two tables with the 3rd table? Something like this:

(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 doesn't work. I could move JOIN inside of each SELECT query before UNION, but would rather try to JOIN with the UNION result.

How can I fix this?

Upvotes: 11

Views: 21706

Answers (2)

Alma Do
Alma Do

Reputation: 37365

Yes, it is possible. However, your code is incorrect since you're missing SELECT statement itself, because your first select becomes a rowset (runtime-created table). I.e. you have to specify SELECT operator and fields that you want to get. 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`

Upvotes: 16

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

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`;

Upvotes: 3

Related Questions