query in sql - 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 2000 Forums

Transact-SQL (2000)

query in sql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-10 : 08:07:08

Prathiba writes "i have a table named loc_state
with the following columns
StateId
StateName
i have another table car_journey
with the following columns
OriginsateId
desstateid
etc
now i want origin statename
and destination statename by comparing stateid with originsttateid and at the same time stateid with destination stateid
what should i do "

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-10 : 08:48:57

Use inner join

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-10 : 17:14:37

Based on the way I see the table design there are a couple of things to keep in mind:
1. There could be more than 1 DESTSTATEID for any particular ORIGINSTATEID. If there weren't then it wouldn't make sense to have the DESTSTATEID field in a seperate table.
2. There could be an ORIGINSTATEID that doesn't have a corresponding DESTSTATEID field.

(You'll notice in both samples below that in order to get the State Names you'll have to refer to the StateTable two times so you have to ALIAS that table. In the sample I alias the table as ST1 and ST2, where ST1 is for the originating state name, and ST2 is for the destination state name)

You have to consider all of the factors about the data, and answer some other questions when you determine which type of JOIN you are going to perform. If you use an INNER JOIN as suggested then you will only see the ORIGINSTATEID's that do have at least 1 match. But will NOT see any ORIGINSTATEID's that don't have an entry for DESTSTATEID.

Select ST1.StateName, ST2.StateName
from StateTable ST1
INNER JOIN DestTable on DestTable.OriginStateId = ST1.StateId
INNER JOIN StateTable ST2 on ST2.StateId = DestTable.DestStateId

You can alternatively use what is referred to as an OUTER JOIN if you really do want to see all of the originating states whether they have a match or not. If you use the following you will simply get the value NULL for the DestStateId if the match can't be found:

Select ST1.StateName, ST2.StateName
from StateTable ST1
OUTER JOIN DestTable on DestTable.OriginStateId = ST1.StateId
INNER JOIN StateTable ST2 on ST2.StateId = DestTable.DestStateId

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources