SELECT of 2 FK in the same table

Question

In view of this scenario:

Imagine a scenario in the field of oceanography, where the table_A , in addition to the primary key, also needs to keep the direction of the wind (dir_wind) and the direction of the undulation (dir_ondulacao). Knowing that the table winds (id_wind, desc_wind) has all possible directions, and both fields dir_wind and dir_wave are also winds, it would be possible for them to keep the id_wind, for future I rescue the dsc_wind from the table wind from them, ie, would be the fields dir_vento and dir_ondulacao foreign keys of the table.

how do I run select?

Answer 1

You should make two relationships with the same table, nicknamed them differently, this way:

SELECT A.Id, V.Dsc_Vento As Dir_Vento, O.Dsc_Vento As Dir_Ondulacao
  FROM tabela_A A
 INNER JOIN tabela_ventos V ON V.Id_Vento = A.Dir_Vento
 INNER JOIN tabela_ventos O ON O.Id_Vento = A.Dir_Ondulacao;

I hope I’ve been able to help.