Consult data in three tables where only two are related

Question

Good afternoon, I will try to explain a situation where I am, I have 3 tables(A,B,C) where A and B are directly related, B and C are also related. I need to get the value of a field in table C where I am consulted data in table A. I tried to do it this way but the result came null:

SELECT h.Id, h.TagHistorian, h.Descricao, um.Sigla ,h.ComentarioHistoria,
   (SELECT plc.TipoVariavel_Id FROM plc WHERE h.Id = plc.Id) AS varplc
FROM historian h
INNER JOIN um ON um.Id = h.UM_Id
INNER JOIN tipovariavel tp ON tp.Id = h.TipoVariavel_Id

Lowers the structure of the tables:

Basically I need to get the description of the "Typovariavel" and the query will be in the table "Historian" which is related to the table "PLC".

Answer 1

I don’t understand very well what you want and you can’t know very well without the values.

To relate would JOINS:

SELECT A.X, A.Y, B.X, B.Y, C.X, C.Y
FROM TAB_A A
LEFT JOIN TAB_B B ON B.PK = A.FK_B
LEFT JOIN TAB_C C ON C.PK = B.FK_C

To "fetch relative value" could be sub-querie:

SELECT A.X, A.Y, B.X, B.Y, 
(SELECT C.PK FROM TAB_C C WHERE C.PK = B.FK_C LIMIT 1 ORDER BY data DESC)
FROM TAB_A A
LEFT JOIN TAB_B B ON B.PK = A.FK_B

In the above case, if there are more records in the relation TAB_B and TAB_C, I used the LIMIT 1 ORDER BY data DESC to bring for example a last date value.

Use the LEFT in his JOIN to bring everything, so you check how the relationship is, and the LIMIT to bring only 1 value, but usually it accuses error in querie.