need help in a join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need help in a join

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-06-20 : 06:53:38

I have three table country, city state
in state country is a foriegn key and allwed not null
in city I have country, and state as foreign keys.Country is allowed not null but state is allowed null
How could i get the result like following

Country State City
AA as ds
sd sd sds
sds sds
sd s sd
qe sds

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 06:54:51

post some sample data from your tables along with columns if you need a quick solution on this.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-06-20 : 07:09:53

try this

SELECT C.Country, S.State, CT.City
FROM Country C
LEFT JOIN State S ON s.Countryid = c.Countryid
LEFT JOIN City CT ON CT.Countryid = c.Countryid OR S.StateId = CT.StateId

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-06-20 : 09:26:47

Get it done via
SELECT C.country_name as CountryName, C.country_id as CountryID,
s.state_province_id as StateProvinceId, S.state_province_name as StateProvinceName,
T.city_name as CityName, T.city_id as CityId
FROM country C join city t on t.country_id = c.country_id
left join state_province s on t.state_province_id = s.state_province_id

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources