CODEWITHSUNDEEP
sqlsql-servert-sql
Farhad-Taran
Farhad-Taran

Reputation: 6512

select multiple columns from two different tables, merge and then insert into another table?

I have the following tables:

T1    T2           T3    T4
ID    ID A B C     ID    
---   --------     ---   ---
1     3  u v w      3
2     4  x y z      4

First I have to select the items from B that have the IDs retrieved from C as so:

SELECT a,b,c FROM T2 WHERE ID IN (SELECT ID FROM T3)

--will return u,v,w
              x,y,z

Now I want to retrieve the IDs in T1 and merge them with each row returned from the above query and insert them into T4:

   1   u v w
   1   x y z
   2   u v w
   2   x y z

Upvotes: 1

Views: 2612

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Use option with CROSS JOIN 

INSERT T4(ID, A, B, C)
SELECT t1.ID, o.A, o.B, o.C
FROM T1 t1 CROSS JOIN (
                       SELECT a,b,c 
                       FROM T2 t2 JOIN T3 t3 ON t2.ID = t3.ID
                       ) o

Demo on SQLFiddle

Upvotes: 2

Konrad Z.
Konrad Z.

Reputation: 1652

If I understood you correctly, this can help:

INSERT INTO T4
SELECT ID, A, B, C 
FROM    (SELECT a,b,c FROM T2 WHERE ID IN (SELECT ID FROM T3)) tab1
        CROSS JOIN T1

Upvotes: 0

Related Questions