result to show desc rather than ref code

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

SQL Server Development (2000)

result to show desc rather than ref code

Author

Topic

matrixity
Starting Member

2 Posts

Posted - 2006-03-16 : 19:48:33

Select TOP 10 
A.ShipmentDate,
A.Carrier, 
A.FromCityCode AS Origin, 
B.FullDesc AS ORIGIN, 
A.ToCityCode AS Destination
From 
A,B
Where 
B.CityCode=A.FromCityCode

I have two tables. One holds the data for shipments and the other holds data of all cities and states.

Table A
ShipmentDate
Carrier
FromCityCode
ToCityCode
ItemID (int)

Table B
CityCode
FullDesc
ItemID (int)

The select statement above works if I am only referencing to receive output for ONE column (as in this case the origin). The moment I deviate from this statement I get all kinds of "wrong" results.

What I'm trying to do is to have the result return the FullDesc of cities rather than it's stored CityCode. I need to do this for both the origin and the destination in the same query.

Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 20:16:51

[code]Select A.ShipmentDate,
A.Carrier,
A.FromCityCode AS Origin,
O.FullDesc AS Origin_Name,
A.ToCityCode AS Destination
D.FullDesc AS Destination_name
From TableA A inner join TableB as O
On O.CityCode = A.FromCityCode
inner join TableB as D
On D.CityCode = A.ToCityCode[/code]

matrixity
Starting Member

2 Posts

Posted - 2006-03-16 : 22:03:15

Thanks very much KH - we have a green light !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources