how to do this with dataset

hi developers

i've a table named users where i store the city id ,

this city id -> citymaster (cityid,stateid)->state master (cityid,stateid) ->country master (countryid,stateid).

now i need to make the query fetches the columns like this

username,cityid,stateid,countryid

how can i do this

Select U.UserName, C.CityID, S.StateID, CT.CountryID
From UserMaster U
Left Outer Join CityMaster C On U.CityID = C.CityID
Left Outer Join StateMaster S On C.StateID = S.StateID
Left Outer Join CountryMaster CT On S.CountryID = CT.CountryID

hope it helps./.

|||

thanks for that but i did like this

select countrymaster.countryname,countrymaster.countryidentity,

statemaster.statename,statemaster.stateidentity,

citymaster.cityname,citymaster.stateidentity,AdminUserMaster.userid,AdminUserMaster.address1,

AdminUserMaster.address2,AdminUserMaster.postcode,AdminUserMaster.email,AdminUserMaster.isactive,

AdminUserMaster.cityidentity,AdminUserMaster.dob,

(AdminUserMaster.firstname+' '+AdminUserMaster.middlename+' '+AdminUserMaster.lastname)asname

from countrymaster,statemaster,citymaster,AdminUserMaster

where countrymaster.countryidentity=statemaster.countryidentity

and citymaster.cityidentity= AdminUserMaster.cityidentity

How to do recursive select in SQL Server 2000

Suppose there's a table named [Category], which has 2 columns:
CategoryID int,
ParentCategoryID int

Each category, except the top most, has a parent category, it's a 1-to-n parent-children relationship.
Now I want to write a stored proc./function that accepts a CategoryID input parameter, and output all the descendent CategoryIDs (son, grand son, ...). How to do that in MSSQL 2000?

You could write a single SELECT statement if you know the number of levels. But this will probably be hard to read and maintain. Best is to write a multi-statement table-valued function that takes the CategoryID as input parameter, gets each descendent until there is none and returns all the rows. This can be easily used in other SELECT statements. The use of multi-statement TVF however has some disadvantages since there will be no statistics for the results and the optimizer will just make a guess for the number of rows. This may produce bad execution plans depending on how you use such a TVF in your query.

If you upgrade to SQL Server 2005 then you can use a recursive common table expression (CTE) to write a single query that will produce the results. This is declarative in nature and may perform better than the TVF approach also. You may want to consider this option if you work with lot of hierarchical data.