Help, please, just make a sample request.

There is a first table in which there are columns спортсмен and серийный_номер_велосипеда .
The second table: год_выпуска , цвет , год_выпуска .

What will be the single request to select an спортсмен , год_выпуска серийный_номер_велосипеда , цвет , год_выпуска ?

    3 answers 3

    Use join tables :

    1. If the list of bicycle serial numbers in both tables is the same (there are no such elements that are in one table, but not in the second one), then you can use the join join ( inner join ):

       SELECT s.sportsman, s.serial, b.color, b.year FROM sportsmen s JOIN bike b ON s.serial = b.serial

      Sample table in sqlfiddle

      The result of the query

      Or:

       SELECT s.sportsman, s.serial, b.color, b.year FROM sportsmen s, bike b WHERE s.serial = b.serial

      The result of the query

    1. If the list of bicycle serial numbers does not match, then when joining tables using an inner join ( join ) you can lose the necessary rows, so you must use left join to save all the rows in the left table:

       SELECT s.sportsman, s.serial, b.color, b.year FROM sportsmen s LEFT JOIN bike b ON s.serial = b.serial

      Sample table in sqlfiddle

      The result of the query

       SELECT с.спортсмен, в.серийный_номер_велосипеда, в.цвет, в.год_выпуска FROM спортсмен с LEFT JOIN велосипед в ON с.серийный_номер_велосипеда = в.серийный_номер_велосипеда;
      • one
        will give an error - will not know from which table in select'e to take the bicycle's serial_number - Denis
      • I do not agree. Left join links the two tables across the serial_number of the серийный_номер_велосипеда field and it is displayed. Try it yourself. I make exactly the same requests to my database and everything works (otherwise I would not have written). - Tennik
      • one
        he will give an error Ambiguous column name , because he will not know from which table in select'e to take the column serial_number of the велосипед - from the table спортсмен or from the велосипед - Denis
      • one
        Yes, indeed, I forgot to add something in the answer ... Now I will correct it - Tennik

      You need a left join instruction

       select t1.*, t2.color, t2.year from t1 left join t2 on t1.serial_num = t2.serial_num