Multiple foreign keys in a single query

Question

Well, I’m having some problems creating a query in mysql that searches several tables in a single query, I searched here in the stack, I found some similar questions, but I couldn’t understand the logic or method used, and the "why?" to be that way.

I have the following tables:

cards:

ID|nome_carta|id_edicao|id_tipo|efeito_desc|atk|def|link_img|id_atributo|id_lrl|quantidade

attributes:

ID|atributo

editions:

ID|nome_edicao|serial

guys:

ID|tipo

How can I perform the query to have the following result ?

Nome_Carta|Nome_Edição|Serial|Tipo|Efeito|ATK|DEF|IMG|Atributo|LRL|Quantidade

Answer 1

Make the call using INNER JOIN:

SELECT a.nome_carta, b.nome_edicao, b.serial, c.tipo, a.efeito_desc, a.atk, a.def, a.link_img, d.atributo, a.id_lrl, a.quantidade FROM cartas a
INNER JOIN edicoes b ON a.id_edicao = b.ID
INNER JOIN tipos c ON a.id_tipo = c.ID
INNER JOIN atributos d ON a.id_atributo = d.ID

In the case of lrl put the id because you did not pass the structure of this table.

Answer 2

Just make the Join between the tables, relating them by the respective foreign keys:

Select
    c.nome_carta,
    e.nome_edicao,
    e.serial,
    t.tipo,
    c.efeito_desc,
    c.atk,
    c.def,
    c.link_img,
    a.atributo,
    c.id_lrl,
    c.quantidade
from cartas c 
inner join edicoes e on e.id = c.id_edicao
inner join tipos t on t.id = c.id_tipo
inner join atributo a on a.id = c.id_atributo

Answer 3

Muril,

Use the query below to get the expected result.

SELECT
    C.NOME_CARTA,
    E.NOME_EDICAO,
    E.SERIAL,
    T.TIPO,
    C.EFEITO_DESC,
    C.ATK,
    C.DEF,
    C.LINK_IMG,
    A.ATRIBUTO,
    C.ID_LRL,
    C.QUANTIDADE
FROM
    CARTAS C 
    INNER JOIN ATRIBUTOS A ON A.ID = C.ID_ATRIBUTO
    INNER JOIN EDICOES E ON E.ID = C.ID_EDICAO
    INNER JOIN TIPOS T ON T.ID = C.ID_TIPO

In this case, joins were performed to relate the table Letters with the others, it is worth mentioning that nicknames were assigned (alias) for the tables (e.g. C, A, E, T).

Hug,