inner join same table twice
Posted on 2006-04-20
I am trying to get "state name" and "desired state name" based on stateid which is in another table. For this i have to do join twice on state table. But when i do that, i don't get any results. I have all values.
can anyone give a soultion for this problem.
Select u.displayname, up.*, a.avatarpic, tc.countryname, ts.statename, isnull(tds.statename,0) as desiredstatename, ut.usertype
from tbl_User u
inner join tbl_userprofile up on u.userid=up.userid
inner join tblCountries tc on tc.countryid = up.country
inner join tblStates ts on ts.stateid = up.state
inner join tblStates tds on tds.stateid = up.desiredstate
inner join tbl_userType ut on ut.usertypeID = u.usertypeID
inner join tbl_avatar a on u.avatarid=a.avatarid
Where u.Userid = @Userid
Thanks in advance