inner join same table twice

Question

inner join same table twice

Reddy-309776

Mr or Mrs. 500

Points: 503
More actions
April 20, 2006 at 11:22 am

#117042

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

The fastest way for you to "debug" this yourself would be to use the view designer in Enterprise Manager...with each join/filter that you check against a column you would be able to see the resultset and figure out where you're going wrong...you could start with the 2 tables/joins that're causing the problem and work your way through to the other tables once you have these resolved...you could then copy and paste the generated script into your proc or whereever you plan to use it...

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 2

I am not 100% sure on how your data looks like but I could take the guess that some of the desired state ID may be null for certain combinations userprofile ????

I would start by changing those inner to left outer joins

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

noel - i like my suggestion better..

"Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime."..

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 4

noeld

SSC Guru

Points: 96590

April 20, 2006 at 12:53 pm

#633359

I can't argue with that

* Noel

Reddy-309776 Mr or Mrs. 500 Points: 503 More actions · Answer 5

My bad, i was looking at the wrong column in results. damn QA.

thought changed the inner join to left join for null cases.

thank your advices

nexttime will check throughly before querying here.

Reddy-309776 Mr or Mrs. 500 Points: 503 More actions · Answer 6

I can't find " view designer in Enterprise Manager".

how do i access it?

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

Expand your database...right click on "views" and you'll see the design window...add the tables you want to query etc...it's a breeze splly. when you want a step-by-step visual - and of course, you also get your "working" query at the end of it all...

**ASCII stupid question, get a stupid ANSI !!!**

Reddy-309776 Mr or Mrs. 500 Points: 503 More actions · Answer 8

Reddy-309776

Mr or Mrs. 500

Points: 503

April 20, 2006 at 3:41 pm

#633378

cool

thanks

Jeff Gray SSChampion Points: 10667 More actions · Answer 9

Educate him further, and he will open a chain of fried fish restaurants.

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

unless he has other fish to fry of course...

sorry Jeff - you caught me on a Friday when my penchant for silly comebacks is at its' worst...

**ASCII stupid question, get a stupid ANSI !!!**