sql server - SQL looking up values on three joined tables -


i have table structure there cars going between buildings built on plots:

cars

car_id  |  origin  |   destination ----------------------------------    1    |        |       c    2    |    b     |          3    |        |       b 

buildings

build_id  |   plot ------------------         |    2    b      |    1    c      |    3 

plots

plot_id  |  coord ------------------------   1      |  "39.9,-75.5"   2      |  "38.3,-74.7"   3      |  "37.8,-76.1" 

i'm trying build query show each car, plot coordinates of origin , destination buildings, this:

 car_id   |   origin_coord   |   dest_coord -------------------------------------------   1       |   "38.3,-74.7"   | "37.8,-76.1"   2       |   "39.9,-75.5"   | "38.3,-74.7"   3       |   "39.9,-75.5"   | "39.9,-75.5" 

this tried don't think i'm approaching right.

select *    (select build_id, plot, coord buildings     inner join plots on plot = plot_id) x right join cars c on c.origin = x.build_id 

could please me understand how lookup/join multiple columns (origin , dest)?

below first thought:

select c.car_id,p1.coord origin_coord, p2.coord dest_coord cars c join buildings b1 on b1.build_id=c.origin join buildings b2 on b2.build_id=c.destination join plots p1 on p1.plot_id=b1.plot join plots p2 on p2.plot_id=b2.plot 

Comments

Popular posts from this blog

java - WrongTypeOfReturnValue exception thrown when unit testing using mockito -

php - Magento - Deleted Base url key -

android - How to disable Button if EditText is empty ? -