Link two tables in MySQL


I'm new with this from the BD. I hope you can help me.

The query is between these 2 tables:

- line -

  id_line |  region | etapa | line | rel_coord | rel_res
  _______   _______   _____   ____   _________   _____

  1       | region1 |   be  | nom  |   brad    | gary   

  2       | region2 |   be  | ali  |   care    | fabian

  3       | region1 |   fe  | dkt  |   ali     | antho

- tr -

    id_tr |  enero | febrero  | marzo|
   ______   _____   ________   _____

I want it to stay like this:

    region | etapa | line | enero | febrero | marzo |
   _______   _____   ____   _____   _______   _____

   region1 |   be  | nom  |        |        |       |

   region2 |   be  | ali  |        |        |       |

   region1 |   fe  | dkt  |        |        |       |

Try it in several ways. One of them in the following way:

SELECT l.region, l.etapa, l.linea, t.enero, t.febrero, t.marzo from line l, tr t;

Resulting in all: (without mostering the records already had region, stage and line)

  region | etapa | line | enero | febrero | marzo |
  _______   _____   ____   _____   _______   _____
asked by mexflow 10.06.2016 в 18:58

2 answers


As mentioned by our colleague Alonso Carrasco to join two or more tables you need to create foreign keys, but if you say you're new, maybe with your comment it's not enough and you need an explanation.


First, to relate tables, you have to be clear about how many types of relationships exist:

  • One-to-one ratio (1: 1): this relationship occurs, for example, between two tables called Usuario and DNI . A DNI can only belong to a Usuario and a Usuario can only have a DNI . So, in general, a foreign key of Usuario to DNI would be created, resulting in a new field in the DNI table that would reference the user's id or vice versa, since in this case it would not matter where to put the outside key

  • One to many relationship (1: n): in this relation the same thing happens as in 1: 1, since for example, between two tables called Usuario and Vehiculo , it would happen that a Vehiculo may have as owner at a Usuario , but a Usuario can have more than one Vehiculo , it is related in the following way:

  • Many to many relationship (n: m): this relationship is a bit more laborious, but it is just as simple to understand. We have two tables called Asignatura and Alumno , so we think and say the magic formula, a Alumno can have many Asignaturas and a Asignatura can have many Alumnos , so this would be a case of relationship many to many. When you come across this relationship, remember that always you have to create an auxiliary table between the two, which collects the id of each table, just like in the following image:

The function of this auxiliary table is that it will collect the ids of each table and link them, for example, a record of this table is:

Student 1 and Subject 1. Student 2 and Subject 1. Student 1 and Subject 3.

And the two would be primary keys.


Once you have this clear, you just need to know how you can join these tables when you want to build a sentence. To join tables you can use the INNER JOIN, in the following way:

SELECT u.nombre, u.direccion, d.numero
FROM Usuario u
ON = d.Usuario_id

After adding FROM 'tabla1' 'acronimo1' , you add INNER JOIN 'tabla a relacionar' 'acronimo' ON acronimo1.'campo enlazado' = acronimo2.'campo enlazado'. Siempre se tienen que enlazar los campos que sean el "mismo", es decir el id de User , siempre sera igual al campo User_id de DNI '.

In case you need to relate more than one table, just keep adding INNER JON ... ON ... = ... , for example, assuming that the Usuario table has a Vehiculo_id field in relation to the id of the Vehiculo table:

SELECT u.nombre, u.direccion, d.titulo, v.marca
FROM Usuario u
ON = d.Usuario_id
INNER JOIN Vehiculo v
ON u.Vehiculo_id =

You can also use different JOIN , in addition to the INNER JOIN there is also the LEFT JOIN , RIGHT JOIN , FULL JOIN , etc ..


Anyway, in case you want to join two sentences that do not have any relation, I recommend you use UNION that allows you just this. For example, in your case, it could be:

SELECT region, etapa, linea FROM line
SELECT enero, febrero, marzo FROM tr;

I hope I have solved and clarified your doubts even more. If you need anything or do not understand something that I explain, please do not hesitate to share it with me. In addition, I encourage anyone who wants to add another case and thus have a more complete entry.

Good luck with your BD!

answered by 10.06.2016 в 20:34

Usually I look when I join two tables I integrate keys foreneas, this way I have never had problems to consult the tables and join them, we will think that in the table; TR you add the key of the LINE table as a key and in the Select you add something like this

SELECT l.region, l.etapa, l.linea, t.enero, t.febrero, t.marzo FROM line l CROSS JOIN tr t

answered by 10.06.2016 в 19:14