Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1078
  • Last Modified:

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
Asked:
midwestexp
1 Solution
 
sdstuberCommented:
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
 
midwestexpAuthor Commented:
Yes i have to include all the 22 Tables, Please explain me How to Do that.
0
 
dbmullenCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
midwestexpAuthor Commented:
Each table have only one column,

create table temp01 (
    a varchar2(1)
)
0
 
sujith80Commented:
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 )) 

Open in new window

0
 
midwestexpAuthor Commented:
Thank You Very Much.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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