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.
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.
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.
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.
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.
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.