beginner JOIN question - 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

General SQL Server Forums

New to SQL Server Programming

beginner JOIN question

Author

Topic

Coolness
Starting Member

4 Posts

Posted - 2009-10-02 : 16:54:45

I have a table of airport pairs, like

ORIGIN	DESTINATION
JFK		SFO
SFO		JFK
MCO		JFK
ORD		SFO

etc. There are a few hundred thousand, so speed is an issue with this question. I'd like to join that to another table with lat/long locations, which looks like

AIRPORT	LAT	LONG
JFK		101.3	57.2
SFO		52.1	110.1  (obviously these aren't accurate)

So that my results are

JFK	101.3	57.2	SFO	52.1	110.1

for each line in the first table of pairs.
I can JOIN one of the airport codes to its coordinates, but how do I end up with results for both pairs like above?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-02 : 17:03:03

SELECT A.Origin, O.Lat, O.Long, A.Destination, D.Lat, D.Long
FROM Aiports A
INNER JOIN Coordinates O ON A.Origin=O.Airport
INNER JOIN Coordinates D ON A.Destination=D.Airport

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources