CODEWITHSUNDEEP
sqlsql-server
Luis Diaz
Luis Diaz

Reputation: 15

Is there a way to JOIN a table consisting of two tables UNIONed together?

SELECT 
    a.First_Name
   ,b.Last_Name

FROM table a
JOIN table b
UNION table c
ON a.ID=b.ID

I'm not even sure how the syntax would be for this. Basically what I want is to UNION table b and c then JOIN the new table to table a.

Upvotes: 0

Views: 68

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

The result of a union can be join to others table if you the the proper join clause

You could use this sintax 

  select a.col1, t.name 
  from table3 c 
  inner join (
        select id, name1 as name
        from table1 a
        union id, name2
        from table2 b
  ) t on. t.id = a.id

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You can join a subquery:

SELECT 
  a.First_Name,
  bc.Last_Name
FROM a
JOIN 
(
  SELECT ID, Last_Name FROM b
  UNION
  SELECT ID, Last_Name FROM c
) bc ON bc.ID = a.ID;

Upvotes: 1

D Stanley
D Stanley

Reputation: 152511

Make the union a subquery:

SELECT 
    a.First_Name
   ,bc.Last_Name
FROM table a
JOIN (
    SELECT * FROM table b
    UNION 
    SELECT * FROM table c
    ) AS bc
ON a.ID=bc.ID

Note that the subquery must have its own alias (bc in this case) which should be referenced in the join clause and the select (meaning you can't reference tables b or c directly outside of the subquery).

Upvotes: 0

Related Questions