Select distance between cities in two directions

All we need is an easy explanation of the problem, so here it is.

I have a distance table and a cities table.
I have joined them in a way where i now have: cityA_id,city_A,cityB_id,cityB, distance in km
i need to get the distance betweens cityA and CITY B and
then get the distance between cityB and CityA in the same query result.
(meaning if Albany to Auburn is 36 Km i want to also check Auburn to Albany distance (where cityA will be Auburn and cityB Albany)
sample data:
Select distance between cities in two directions

how can i write this affectingly?

what i have so far:

with A as (
SELECT  [CITY_1],
[CITY_NAME]
,[CITY_2]

,[DIS_IN_KM]

FROM [DB].[dbo].[DISTANCE] d
join cities c
on d.CITY_1 = c.CITY_NO
),
first_direction as 
(
select A.[CITY_1] as cityA_id,A.[CITY_NAME] as [CityA],A.[CITY_2] as CityB_id,  c.CITY_NAME as CITYB, A.[DIS_IN_KM] as [distance in km],
c.CITY_NAME as CITYB2, A.[CITY_NAME] as CITYA2--, B.DIS_IN_KM as [distance in km opposite]

from A join CITIES c on A.CITY_2 = c.CITY_NO

)
select * from first_direction

tables schema:

CREATE TABLE [dbo].[DISTANCE](
    [CITY_1] [char](6) NOT NULL,
    [CITY_2] [char](6) NOT NULL,
    [DRIVE_TIME] [decimal](9, 2) NOT NULL,
    [DIS_IN_KM] [smallint] NOT NULL,
    [HARD_DIST] [smallint] NOT NULL,
    [BUS_FARE] [decimal](9, 3) NOT NULL,
    [CODE1] [smallint] NOT NULL,
    [CODE2] [smallint] NOT NULL,
    [CODE3] [smallint] NOT NULL,
    [DATE] [datetime] NOT NULL,
    [TEMP1] [decimal](9, 3) NOT NULL,
    [TEMP2] [decimal](9, 3) NOT NULL,
    [TEMP3] [decimal](9, 3) NOT NULL,
 CONSTRAINT [PK_DISTANCE2] PRIMARY KEY NONCLUSTERED 
(
    [CITY_1] ASC,
    [CITY_2] ASC
)

CREATE TABLE [dbo].[CITIES](
    [CITY_NO] [char](6) NOT NULL,
    [CITY_NAME] [char](40) NOT NULL,
    [BASE_CODE] [tinyint] NOT NULL,
    [ZONE_CODE] [tinyint] NOT NULL,
    [NAME_2] [char](40) NOT NULL,
 CONSTRAINT [PK_CITIES_NUMBER] PRIMARY KEY NONCLUSTERED 
(
    [CITY_NO] ASC
)

Thanks in advance!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I believe your CTE already gives you the list of all distances between any two Cities, so you just need to join the CTE to itself on reversed City fields to get the reverse results like so:

WITH CityDistances AS 
(
    SELECT 
        CITYA_id,
        CITYA,
        CITYB_id,
        CityB,
        DIS_IN_KM
    FROM DB.dbo.DISTANCE d
    INNER JOIN cities c
        ON d.CITY_1 = c.CITY_NO
)

SELECT 
    CD1.CITYA_id,
    CD1.CITYA AS [starting point],
    CD1.CITYB_id,  
    CD1.CITYB as destination, 
    CD1.DIS_IN_KM AS [distance in km],
    CD2.CITYA_id AS [CITYA_id 2], -- Note you can call this CITYB_id instead if that's clearer to you (CD2.CITYA_id = CD1.CITYB_id)
    CD2.CITYA AS [starting point 2],
    CD2.CITYB_id AS [CITYB_id 2], -- Note you can call this CITYA_id instead if that's clearer to you (CD2.CITYB_id = CD1.CITYA_id)
    CD2.CITYB AS [destination 2], 
    CD2.DIS_IN_KM AS [distance in km 2],
FROM CityDistances AS CD1
INNER JOIN CityDistances CD2
    ON CD1.CITYA_id = CD2.CITYB_id
    AND CD1.CITYB_id = CD2.CITYA_id

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply