How to reduce number of joins on same table

Database
Enthusiast

How to reduce number of joins on same table

Hi

I am working on sql query where same table is used in join however with different conditions each time.

The table is lookup table which stores info like ID,DESC...

So I am joining on ID and fetching desc.

Eg:

 

SELECT tb1.desc, tb2.desc,tb3.desc...tb50.desc

from main_table mt1

inner join main_table mt2 on ...

inner join main_table mt3 on ...

left outer join lkup_tbl tb1 on tb1.id = mt1.id1

left outer join lkup_tbl tb2 on tb2.id = mt1.id2

left outer join lkup_tbl tb3 on tb3.id = mt2.id3

... 

left outer join lkup_tbl tb50 on tb50.id = mt2.id40;

 

I don't want to use 50 LEFT OUTER JOINS. In Joins only the column on right side is different.
Kindly suggest efficient way of doing this.

 

TIA

Nitin

 

6 REPLIES
Teradata Employee

Re: How to reduce number of joins on same table

Are you saying that the user is looking for the Desc in only one of those 50 tables?  If that is the case then you want to construct a dynamic query in a stored procedure or other application code, or write a shell script to construct the query if you are using Bteq.  You could also use inner join to just one lookup table - either it's there or it isn't.

Enthusiast

Re: How to reduce number of joins on same table

Hi
Thanks for reply.
No, the requirement is to fetch 50 description for 50 ID columns. ID columns are present in 3 main tables. However description column is present in same lookup table. I am joining main tables and lookup table on the basis of ID columns. I don't want to do lookup 50 times by adding join one for each time. Is there any other way I can group some table joins or any better way of doing it.

Thanks
Nitin
Teradata Employee

Re: How to reduce number of joins on same table

I still don't really understand the issue, but you might consider creating a join index.  When the system gets a query that uses only columns that are defined in the join index, it doesn't even bother reading and joining the tables - it just reads the join index, which is just like reading one table.  (When the base tables are updated, of course the JI is updated too, and that is some overhead, but things like ID and Desc usually don't change very often.)  See "Create Join Index" in the chapter on Index Statements in the SQL Data Definition Language manual.

Enthusiast

Re: How to reduce number of joins on same table

May be I m not explaining the problem in right manner. I'll rephrase it.
If you refer to my initial post , you can see in left outer join same table is used all the time. However join condition is different.
I am looking for another way of writing SQL where I don't have to write left outer join 50 times.
Teradata Employee

Re: How to reduce number of joins on same table

You are flattening a heirarchy or some number of attributes for a key column from the main table.

 

There is no other way of doing this other than the joins, based upon the columns in the tables you have presented.

 

You could do it in groupings or phases if you are running into system limitations and then combine the final results.

 

thanks

 

Dave

Visitor

Re: How to reduce number of joins on same table

You can write CASE ELSE statements in SELECT clause and put all joins with 'OR' instead of 'AND' in WHERE clause:

 

Something like this:
SELECT case when tb1.id = mt1.id1 then tb1.desc,
case when tb2.id = mt1.id2 then tb2.desc,
case when tb2.id = mt3.id2 then tb3.desc...tb50.desc

from main_table mt1

inner join main_table mt2 on ...

inner join main_table mt3 on ...

left outer join lkup_tbl tb1 on
(tb1.id = mt1.id1 or tb1.id = mt1.id2 or tb1.id = mt2.id3 .... or tb1.id = mt40.id40)
;


But query performance might impact due to ORed join conditions.