SQL Server Performance Forum – Threads Archive

SQL Query.

I have three tables namely country,state and city.
I have relation among three tables as follows: country.countrycode=state.countrycode (i:e) a state belongs to a country
state.statecode=city.statecode (i:e) a city belongs to a state. Now i have a webpage where i’m asked to show countries->states->cities.
i have used a join query as follows: select countryname,statename,cityname from country,state,city where country.countrycode=state.countrycode and state.statecode=city.statecode This works fine incase of countries which has both states and cities.My problem is if a country doesn’y have state at all,this query is not listing that,similarly if a state doesn’t have city at all,the state is not listed. I need help in writing a query which satisfies both the conditions.Any help will be greatly appreciated.

---

You are using inner joins and it returns only rows that has corrisponding rows in other table/s.
SELECT countryname,statename,cityname
FROM country
INNER JOIN state ON country.countrycode = state.countrycode
INNER JOIN city ON state.statecode = city.statecode
You need to use outer joins to view rows that does not have a corrisponding row in another table/s.
SELECT countryname,statename,cityname
FROM country
LEFT OUTER JOIN state ON country.countrycode = state.countrycode
LEFT OUTER JOIN city ON state.statecode = city.statecode
Bambola.

---

Great!!!Thanks a lot for your fast response.I’m able to acheive both the conditions.

---

]]>