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:

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
)
``````

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 🙂