CODEWITHSUNDEEP
sqloracle-databasejoin
user2424380
user2424380

Reputation: 1473

Oracle SQL create duplicated rows with join

I would like to create duplicated rows as an output: first I would like to have the row, and then the same row again joined with another table, like this:

table A with fieldA (and lots of others)

table B with fieldB (and lots of others)

and the output:

fieldA1          (and all the fileds from tableA)      
fieldA1 fieldB1  (and all the fields from tableA and tableB joined) 
filedA1 fieldB2  
fieldA2 
fieldA2 fieldB8   
filedA2 fieldB9   
. . .

I was thinking about using union, but then I would have to duplicate the very complicated select of tableA to get the rows of tableA and tableB (tableA is union of other tables, I just simplified it for the question). Is there any 'cleaner' solution to this? I know it is an unusual question, so I would appriciate any thougts or ideas.

Thank you very much in advance!

Upvotes: 1

Views: 1563

Answers (2)

DCookie
DCookie

Reputation: 43533

Modify Benoit's answer to use a common table expression:

WITH A as (
 your select for "A"
)
SELECT A.fieldA, B.fieldB, A.*, B.*
  FROM A LEFT JOIN B ON 1 = 0
UNION ALL
SELECT A.fieldA, B.fieldB, A.*, B.*
  FROM A JOIN B ON (join condition)

Upvotes: 3

Benoit
Benoit

Reputation: 79235

Use:

SELECT A.*, B.*
  FROM A LEFT JOIN B ON 1 = 0
 UNION ALL
SELECT A.*, B.*
  FROM A INNER JOIN B ON (join condition)

Upvotes: 1

Related Questions