CODEWITHSUNDEEP
sqlrelational-database
user399950
user399950

Reputation:

SQL Question: select cities distance without duplicates

I have a table with the columns: city, x, y

I need to select the distance between all the cities without duplications the results should look like this city1,city2,distance

but I shouldn't get city2,city1,distance ( since I already got the distance between those cities...

what is the correct SQL query for that... thanks!

Upvotes: 2

Views: 1844

Answers (3)

Florian Reischl
Florian Reischl

Reputation: 3866

What you are trying to do is called a "running total".

Be careful with triangular joins like ON c1.CityID > c2.CityID, they create an cartesian product! (I don't want to argue with anybody else's answer.)

Have a look at Jeff Moden's world best article about running totals: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten).

If you don't have an account you will have to create one (only an email address is needed). However, I promise it's worth!

Upvotes: 1

Tom H
Tom H

Reputation: 47464

SELECT
    C1.city,
    C2.city,
    <distance calculation>
FROM
    Cities C1
INNER JOIN Cities C2 ON
    C2.city_id > C1.city_id

I left the distance calculation blank since it depends on whether you're doing "as the crow flies", using street routes, accounting for curvature of the earth, etc.

Upvotes: 3

Amy B
Amy B

Reputation: 110121

SELECT c1.City, c2.City, dbo.CalcDistance(c1.x, c1.y, c2.x, c2.y)
FROM Cities c1, Cities c2
WHERE c1.CityID > c2.CityID

Upvotes: 4

Related Questions