?
Solved

inner join same table twice

Posted on 2006-04-20
6
Medium Priority
?
3,724 Views
Last Modified: 2007-11-27
Hi
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.
Query:
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
0
Comment
Question by:coolcyber
6 Comments
 
LVL 12

Assisted Solution

by:wstuph
wstuph earned 80 total points
ID: 16500317
Do you get results if you change it to a left outer join?  If you're inner joining and it's not there then it's not going to show up.
0
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 100 total points
ID: 16500319
If the desired state is null then you won't get any record for the user.  Instead of using INNER JOIN, try using a LEFT JOIN:

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     tbl_userType ut on ut.usertypeID = u.usertypeID
inner join      tbl_avatar a on u.avatarid=a.avatarid)
left join tblStates tds on tds.stateid = up.desiredstate
Where     u.Userid = @Userid
0
 
LVL 20

Accepted Solution

by:
Sirees earned 320 total points
ID: 16500327
Can you try this???

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
left join     tblStates ts on ts.stateid = up.state
left 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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:coolcyber
ID: 16500364
when i use "left join" i get a result  but with "NULL" in desiredstate column.
But i have a value in desiredState column
0
 
LVL 1

Author Comment

by:coolcyber
ID: 16500380
Sirees:
tried that, but no result in desiredstate column
0
 
LVL 1

Author Comment

by:coolcyber
ID: 16500512
my bad i was looking into wrong column
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question