开发者

Oracle SQL create duplicated rows with join

开发者 https://www.devze.com 2023-04-09 14:09 出处:网络
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:

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)
0

精彩评论

暂无评论...
验证码 换一张
取 消