Select with value lookup in another table

Question

I have a table with foreign key fields, and would like to return in a select the value of the item for the key.

For example, I have the following table formed with the query:

SELECT cod_produto,
MQ1FK,
MQ2FK,
MQ3FK,
MQ4FK,
MQ5FK
FROM engenharia_processo 
INNER JOIN engenharia_produto 
ON cod_produto = codigo;

    cod_produto     MQ1FK  MQ2FK  MQ3FK  MQ4FK  MQ5FK
    0101500063500   18     5       null   null  null
    0101500083500   1      3       4      null  null

In another table I have the data:

    MQPK | Valor
    1      2
    3      5
    4      3
    5      9
    18     7

I would like to perform a query that returns the table with the Value field instead of the key, type:

cod_produto     MQ1FK  MQ2FK  MQ3FK  MQ4FK  MQ5FK
0101500063500   7      9       null   null  null
0101500083500   2      5       3      null  null

I tried to use:

select valor from engenharia_maquina where MQPK = (select MQ1FK from engenharia_processo);

But as the return has more than one line not of the right.

Answer 1

If you guarantee that the relationship is 1 to 1, that is, there is only one MQPK for each MQ1FK, simply add TOP 1 to the sub-consumption:

select valor from engenharia_maquina where MQPK = (select TOP 1 MQ1FK from engenharia_processo);

Otherwise, what you need is a Join. You will have multiple occurrences of the main table data, as this data will be repeated for each row of the table with foreign key - so maybe you want a query only for the main table, and a separate query only for the "daughter" table data. Something like:

SELECT
    engenharia_processo.cod_produto,
    engenharia_maquina.valor
FROM engenharia_maquina
INNER JOIN engenharia_processo
    ON engenharia_maquina.MQPK = engenharia_processo.ID