Lifewithsun Lifewithsun - 2 years ago 74
SQL Question

Which join should i use for getting record from 4 tables

I have four tables which is like this

---user table---|--Form table-----|---Request status-- |--Request Table--
UserId Username |FormId FormTitle |RequestId StatusId |RequestId Title UserId FormId
1 Test | 1 Form1 | 1 1 | 1 Request1 1 1
2 User | 2 Form2 | 2 1 | 2 Request2 1 1


I want to pull the record from table like this

RequestId FormTitle UserName Status
1 Test 1 1


But currently I am getting multiple record for below query

select r.RequestId,f.FormTitle,u.UserId, rs.StatusId from request r
Join requeststatus rs on rs.StatusId=rs.StatusId
left join [user] u on u.UserId=u.UserId
left join form f on f.FormId=f.FormId
where r.RequestId=1


Which join should I user to achieve this?

Answer Source

You just have a little typo in your JOIN-condition. Try to format your statements. It will be easier to support and analyze them later.

In your case, try:

           SELECT r.RequestId
                 ,f.FormTitle
                 ,u.UserId
                 ,rs.StatusId
             FROM request r 
  LEFT OUTER JOIN requeststatus rs 
               ON rs.RequestId  = r.RequestId
  LEFT OUTER JOIN user u 
               ON u.UserId      = r.UserId             
  LEFT OUTER JOIN form f 
               ON f.FormId      = r.FormId         
            WHERE r.RequestId   = 1

Your JOINS won't work if you do like:

u.UserId=u.UserId

As you are joining columns from the same table "u"

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download