• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1079

# Cartesian-Join with the blank table

Sample Query:

SELECT     *
FROM       TEMP01,TEMP02,TEMP03,TEMP04,TEMP05,TEMP06,TEMP07,TEMP08,TEMP09,TEMP10,
TEMP11,TEMP12,TEMP13,TEMP14,TEMP15,TEMP16,TEMP17,TEMP18,TEMP19,
TEMP20,TEMP21,TEMP22

I have to DO the Cartesian-Join with all the 22 Tables, The problem is if any one of the table is
blank then the result set also empty,

How can i handle this?. In the table set any table/Combination can be blank.

0
midwestexp
1 Solution

Commented:
you would have to do an outer join to any table that might be missing data.

however,  are you sure you really need to do a 22-way join?
0

Author Commented:
Yes i have to include all the 22 Tables, Please explain me How to Do that.
0

Commented:
is this a trick question?  I'm trying to think of a reason to ever do this.
do all of the tables have the same number of columns?
what is the ddl for temp01?

0

Author Commented:
Each table have only one column,

create table temp01 (
a varchar2(1)
)
0

Commented:
Cartesian join of 22 tables will be a bad idea, if they are big tables. Probably you can do something programmatically to achieve the actual goal behid this.
To answer the question you may use a query like this, extend it for the 22 tables.
``````select * from
(select * from TEMP01 union all select null from dual where not exists(select 1 from TEMP01 )) ,
(select * from TEMP02 union all select null from dual where not exists(select 1 from TEMP02 )) ,
(select * from TEMP03 union all select null from dual where not exists(select 1 from TEMP03 ))
``````
0

Author Commented:
Thank You Very Much.
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.