Do Select with value lookup in another table

Question

Do Select with value lookup in another table

Navigation

#1 by (4 votes)

1

I have a table with foreign key fields, and would like to return a select value of the item to 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 in place 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 since the return has more than one line is not right.

mysql sql

asked by anonymous 17.11.2014 / 18:47

1 answer

Problems with html select Include in absolute address

score 4 · Accepted Answer

If you make sure the relationship is 1 to 1, that is, there is only one MQPK for each MQ1FK, you should add TOP 1 to the subquery:

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 foreign key table - so you may want to query only the main table, and a separate query only for the data in the "daughter" table. Something like:

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