Oracle SQL create duplicated rows with join
I would like t开发者_开发知识库o 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!
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)
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)
精彩评论