0

I have a business table and in that we have 50 foreign key columns which refers other master data tables. to fetch all the data my query has to join all the 50 reference tables like

select ct.id , ct.name , ct.description , st.value , pr.value , sv.value , ....
from 
core_table ct 
left outer join domain_value st on ct.status_fk = st.id
left outer join domain_value pr on ct.priority_fk = pr.id
left outer join domain_value svon ct.severity_fk = sv.id
.......
.......

so like this i need to make 50 left outer joins. is this right to do 50 left outer joins like this or do we have any other optimized way to achieve this ?

ashok
  • 1
  • I would ask why you have 50 foreign key columns in one table, is this a normalization issue? – Paul C Dec 21 '11 at 10:58
  • here its something like the record will have reference to 50 master data. so i choose to keep it in the same table. thats the requirement. – ashok Dec 21 '11 at 14:44

1 Answers1

0

Is too many Left Joins a code smell?

It's a perfectly legitimate solution for some designs.

Community
  • 1
  • 1
Paul C
  • 4,687
  • 5
  • 39
  • 55
  • thanks "CodeBlend" for replying .. so are you saying that doing 50 to 100 lef joins is perfectly ok ? i see this table can get extended max to 100 reference fields not more than that. so is 100 left outer join also wont cause any performence issues ? basically upon this understanding only i can proceed with my design !!!! – ashok Dec 21 '11 at 14:05