I have a table of "roads" and "cities" in the database. The beginning and end of the road table is the id of cities. I can not add links:
roads - prntscr.com/aspd78 and cities - prntscr.com/aspdi3 .
I need a table where the columns are:
city.id, city.name, city.x, city.u, roads.distance. Example prntscr.com/aspdsh This is a query:

(SELECT * FROM города where id in (select начало from дороги where конец= "5")) UNION (SELECT * FROM города where id in (select конец from дороги where начало=" 5 "))

But in this table there is no "road. Distance". If at least one of in in the query works, then I need to take a distance from the string and add it to the table.

I tried to do this:

 (SELECT города.id, города.название , города.x , города.y, дороги.расстояние FROM города , дороги where города.id in (select начало from дороги where конец= "5")) UNION (SELECT города.id, города.название , города.x , города.y, дороги.расстояние FROM города , дороги where города.id in (select конец from дороги where начало="5"))
  • Do you need the sum of the lengths of roads leaving the city? then no in is needed, do join cities with roads by condition начало=городId or конец=городId , group by cities and take sum (distance) - Mike
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

 (SELECT города.*, дороги.расстояние FROM города INNER JOIN дороги ON города.id = дороги.начало WHERE дороги.конец = 5) UNION (SELECT города.*, дороги.расстояние FROM города INNER JOIN дороги ON города.id = дороги.конец WHERE дороги.начало = 5)
  • Thank . Works - JKSDS