1

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: enter image description here

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!

3
  • Posting the DDL to create your Table Schema, or at least providing each tables column definitions would make this a lot easier for us to help please.
    – J.D.
    Commented Apr 13, 2021 at 10:29
  • @J.D. you are right, thanks for the comment. i added the info accordingly.
    – sql_girl
    Commented Apr 13, 2021 at 10:39
  • 1
    Thanks! I posted an answer. I'll review your update and if I need to change anything in my answer I will, but I think it should be what you're looking for.
    – J.D.
    Commented Apr 13, 2021 at 10:55

1 Answer 1

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
2
  • Thank you, it seems to work.I guess i over complicated things :)
    – sql_girl
    Commented Apr 13, 2021 at 10:58
  • 1
    @sql_girl No problem! Sometimes it's helpful to take a step back and see what pieces you have so far, like the CTE which is already a list of all city to city distances (in both directions). And then think how to transform it from where it is to where you want it to be. It takes getting used to, and for me I'm very visual, so staring at the results of what I have so far to try to connect the dots to the next step helps me a lot. But anyways, I've been working in SQL for a while now, so I'm used to these kind of problems. You'll get to used to it too! 🙂
    – J.D.
    Commented Apr 13, 2021 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.