I have the following tables:
Letters:
ID|nome_carta|id_edicao|id_tipo|efeito_desc|atk|def|link_img|id_atributo|id_lrl|quantidade
Attributes:
ID|atributo
issues:
ID|nome_edicao|serial
types:
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
Murilo,
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, the joins were performed to relate the Letters table to the others, it is worth noting that nicknames ( alias ) were assigned to the tables (eg C, A, E, T) .
Hugs,
Make the connection 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 I put id because you did not pass the structure of this table.
Just do the join between the tables, relating them to their 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