Display field from another table in SQL

Question

I'm a newbie with SQL...

Now I want to display some instances of AddrDistances from DevExpress CxGrid with SQL.

Select Cast((DistanceAsMeters * 0.001) as Decimal(8,1)) DistanceAsKm, bold_id, created, fromAddress, toAddress
From AddrDistance
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
       and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask)
Order By Created Desc

This SQL is working and the result is:

DistanceAsKM Bold_ID    Created      FromAddress ToAddress
0            134808     16.02.2010   121795      134570 
0            121701     10.03.2010   120850      122991   

The result I want is this:

DistanceAsKM Bold_ID    Created      FromAddress ToAddress
0            134808     16.02.2010   Kalmar      Stockholm 
0            121701     10.03.2010   Falkenberg  Oslo   

So the amount of rows is right but I want to replace the numbers in FromAddress and ToAddress with strings from another table. The numbers shows here is just the boldid. Every object in the database have an unique boldid. The addresses above is stored in table Address and it have a City field with the column and a boldid as a key.

What should I write in SQL to get this right ? Is there something in the CxGrid that could help here ?

Regards

EDIT1: After some experimenting I got this SQL that seems to work:

SELECT DistanceAsMeters, Bold_Id, Created, AddressFrom.CityName_CO as FromCity, AddressTo.CityName_CO as ToCity
FROM AddrDistance
LEFT JOIN Address AddressFrom ON AddrDistance.FromAddress = AddressFrom.Bold_Id
LEFT JOIN Address AddressTo ON AddrDistance.ToAddress = AddressTo.Bold_Id
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
       and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask)
Order By Created Desc

Thanks a lot for the help! Peter got the answer as I think it was most clear. BTW I use InterBase 2009 and I think it use SQL dialect 3. One difference was that it don't handle AS after JOIN.

Answer 1

You will need to create a JOIN on the City table.

Select Cast((DistanceAsMeters * 0.001) as Decimal(8,1)) DistanceAsKm, bold_id, created, fromAddress, toAddress 
From AddrDistance 
**JOIN City c
ON ad.bold_id = c.bold_id  -- Or whatever the column is**
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0 
       and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask) 
Order By Created Desc

• without the *'s :o)

Answer 2

try joining on those IDs:

Select
    Cast((DistanceAsMeters * 0.001) as Decimal(8,1)) DistanceAsKm, bold_id, created
        ,f.DisplayText, t.DisplayText                       ---<<<<<<
    From AddrDistance
        JOIN OtherTable f ON AddrDistance.fromAddress=f.PK  ---<<<<<<
        JOIN OtherTable t ON AddrDistance.toAddress=t.PK    ---<<<<<<
    Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
        and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask)
    Order By Created Desc

Answer 3

You want to join in the other table. You didn't specify a sql variant, but from looking at the syntax it looks like SQL Server / TSQL.

Try:

Select Cast((DistanceAsMeters * 0.001) as Decimal(8,1)) DistanceAsKm, bold_id, created, AddressFrom.AddressName, AddressTo.AddressName
From AddrDistance
JOIN Address AS AddressFrom ON AddrDistance.fromAddress = AddressFrom.AddressId
JOIN Address AS AddressTo ON AddrDistance.toAddress = AddressTo.AddressId
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
       and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask)
Order By Created Desc