Need help in SELECT statement from multiple tables

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

.NET Inside SQL Server (2005)

Need help in SELECT statement from multiple tables

Author

Topic

Ravius
Starting Member

13 Posts

Posted - 2009-08-19 : 02:35:52

Hi guys, I am currently doing a select statement for a function to display Countries, Cities and States by requesting the user to input the state name.

I got 4 tables actually.
Countries -- CountryID, Name
Cities -- CityID, Name, CountryID(FK)
States -- StateID, Name, CountryID(FK), CityID(FK)
Location -- LocationID, CountryID(FK), CityID(FK), StateID(FK)

How to I join these table to display the Name of Country,City and state with just taking in state name as parameter?
How do I make it so that every line of the output (with the names in each line) is a locationID?

I really stucked for a very long time. Hope you guys can help me. Thx in advance (:

red108
Starting Member

6 Posts

Posted - 2009-08-19 : 05:28:12

SELECT CN.[NAME] AS CountryName,
CT.[NAME] AS CityName,
ST.[NAME] AS StateName
FROM COUNTRIES CN
INNER JOIN CITIES CT ON CN.COUNTRYID=CT.COUNTRYID
INNER JOIN STATES ST ON CN.COUNTRYID=ST.COUNTRYID
WHERE ST.[NAME]=@stateName --- your input parameter

Ravius
Starting Member

13 Posts

Posted - 2009-08-20 : 04:10:37

hey red! thanks very much. it worked!
oh ya btw.. if the user may choose to type a letter to search the state
e.g if user type S, it will return all the state starting wif letter S
May i know how can i do it?

jaywhy
Starting Member

1 Post

Posted - 2009-08-20 : 05:14:41

Using Red108s solution above is the best way to build Sql statements manually, it helps you understand how a sql statement is constructed! Alternatively, if you have management studio, click on the icon that has a tooltip of 'Design Query in Editor' and add the tables you need. From those tables select the columns you need. You can review the Sql to see how Sql Server built it and execute it to see the results. The sql can then be copied if you need to do so.

A simple way to filter as you describe above it to use the like command

WHERE (States.Name LIKE 'S%')

Ravius
Starting Member

13 Posts

Posted - 2009-08-20 : 05:26:48

haha thx jay for the recommendation (:

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-20 : 05:56:51

BTW, those are "tables" not normalised. This is a common problem, and it leads to limitations in the future, both in code, and in navigation. Horrendous to fix after they have been created and populated. Also City is a child of state, not the other way around (except for Singapore).

You may want to consider that there is an interational standard char(3) ContryCode, and use that instead of CountryId. They should be:

Country (CountryCode, Name)
CountryState (CountryCode [FK], StateCode, Name)  
-- state "ID" is redundant/not required when a perfectly good short form exists, and is demanded anyway
StateCity (CItyId, CountryCode [FK], StateCode [FK], Name)
CityLocation (CityId [FK], Name)
-- likewise LocationId is not required; Name is the key.

Now you can look up any Location/City/State/Country based on natural, supplying just one character or more, using recognisable keys, and eliminate those Identity columns altogether. It is a database, right, not a spreadsheet. Where the column width becomes onerous (City), sure, use a Identity column.

Cheers

Anything worth doing, is worth doing right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources