Getting different address within same table

November 14, 2022

I’m trying to show the miles between an origin and destination. Problem is I’m having trouble writing the query to properly show the destination address. There is only one location table which holds the address of both origin and destination. What am I doing wrong here?

I thought I would get the proper address for the destination and/or location but it’s not working.

select  dist.DistanceId,
        dist.OriginLocationId,
        l.Address,
        dist.DestinationLocationId,
        l.Address,
        dist.DistanceInMiles        
from DistanceMatrixFromGoogleMapsApi as dist
join locations as l
on l.id = dist.OriginLocationId or l.id = dist.DestinationLocationId
    where OriginLocationId in (30506,53097,30362,30505,30377,30378,30594,30379,30514,30518,30517,55055,30360,30515,30516,30616,30512,30511,30510,30513,30381,49129)
        and DestinationLocationId in (53097,30362,30505,30377,30378,30594,30379,30514,30518,30517,55055,30360,30515,30516,30616,30512,30511,30510,30513,30381,49129,34690)

These are the results I get

>Solution :

As suggested by @Larnu –

SELECT
    dist.DistanceId,
    dist.OriginLocationId,
    orig.Address,
    dist.DestinationLocationId,
    dest.Address,
    dist.DistanceInMiles        
FROM DistanceMatrixFromGoogleMapsApi as dist
JOIN locations as orig
    ON dist.OriginLocationId = orig.id
JOIN locations as dest
    ON dist.DestinationLocationId = dest.id
WHERE dist.OriginLocationId in (30506,53097,30362,30505,30377,30378,30594,30379,30514,30518,30517,55055,30360,30515,30516,30616,30512,30511,30510,30513,30381,49129)
AND dist.DestinationLocationId in (53097,30362,30505,30377,30378,30594,30379,30514,30518,30517,55055,30360,30515,30516,30616,30512,30511,30510,30513,30381,49129,34690)