Multiple joins on the same table

[questionary]

Hi.

I have a question to advanced SQL programmers/users. I would be very grateful if you could explain me in simple words why we use multiple joins on the same table (for example multiple left outer joins) - what benefits it brings?

examples would be appreciated

best regards
q

[urtrivedi]

There is no rule for using multiple join. It depends on the structure of your table. If your table has any column which referes to pk of same table. then you need to use multiple join in order to retrive information.

[MeSampath]

Hi,

Based on the requirement we need to use the LEFT, RIGHT joins.

Here is a simple example,
Employee table:
empid empname Addressid
1 A 1
2 B null
3 C 0

Address table:
addressid street City
1 A C1
2 B C2
3 C C3

Here we need to bring all the employees along with addresses. To achieve this we need to use LEFT join here.
LEFT join will fetch all the records from LEFT and matching records from RIGHT.

Here is the query,
select emp.empid, emp.empname, emp.addressid, ad.street, ad.city from employee emp LEFT Join addresses ad ON
emp.addressid=ad.addressid

output
empid empname addressid street city
1 A 1 A C1
2 B NULL NULL NULL
3 C NULL NULL NULL

For more detail about joins, have a look at
http://www.w3schools.com/sql/sql_join_left.asp

Good luck.