How to avoid joining with the same table many times in a query ?

Question

Hi Friends

My Problem is in a table i have 4 reference of the user ID

1. Owner ID
2. Assigner ID
3. Taker ID
4. Evaluator ID
5. Final Evaluator ID

& all these column values are FK to User detail table where i am having there name. If i want to have there name along with their ID while retrivieng the complete row I am joining 4 times with the user details table.

Is there any better Way

Thanks

Answer 1

If you are using it multiple times consider creating a view - it will save some time

Answer 2

That is the right way to do it.

If you want to make life easier for yourself you can use an abstraction layer on top of SQL, such as LINQ to SQL. This can be set up to create the joins for you automatically based on the foreign key constraints. But if you are writing directly in SQL then you have to write the joins yourself.