Problem in SELECT INNER JOIN. Appearing ID instead of name

Question

Hello. I would like a SELECT INNER JOIN result to show the record name instead of the record ID. I have researched several topics dealing with the subject here, but I cannot solve this problem. I have 4 tables where in case 3 of them are related to a table.

Below follows a description of the 4 tables mentioned:

tbl_tipos
----------------
tipo_id
tipo

tbl_categorias
-----------------
categoria_id
categoria

tbl_subcategorias
------------------
subcategoria_id
subcategoria
categoria_fk

tbl_produtos
------------------------
produto_id
tipo_fk
categoria_fk
subcategoria_fk
nome
descricao

What happens is that when you make the SELECT INNER JOIN as below, the tbl_products-related registry name does not appear, but the ID does:

SELECT tbl_produtos.*, tbl_tipos.tipo, tbl_categorias.categoria, tbl_subcategorias.subcategoria
FROM tbl_produtos
INNER JOIN tbl_tipos ON tbl_produtos.tipo_fk = tbl_tipos.tipo_id
INNER JOIN tbl_categorias ON tbl_produtos.categoria_fk = tbl_categorias.categoria_id
INNER JOIN tbl_subcategorias ON tbl_produtos.subcategoria_fk = tbl_subcategorias.subcategoria_id;

Upshot:

How do I show the name instead of the ID? Thank you.

Answer 1

Try to do this way, I gave an organized in consultation for better understanding, I recommend you use Aliases to make the consultation clearer and easier to understand.

SELECT A.nome, A.descricao, B.tipo, C.categoria, D.subcategoria               
FROM tbl_produtos A
INNER JOIN tbl_tipos B ON A.tipo_fk = B.tipo_id
INNER JOIN tbl_categorias C ON A.categoria_fk = C.categoria_id
INNER JOIN tbl_subcategorias D ON A.subcategoria_fk = D.subcategoria_id;

Answer 2

SELECT P.*
      ,T.tipo
      ,C.categoria
      ,S.subcategoria
  FROM       tbl_produtos      P
  INNER JOIN tbl_tipos         T ON P.tipo_fk         = T.tipo_id
  INNER JOIN tbl_categorias    C ON P.categoria_fk    = C.categoria_id
  INNER JOIN tbl_subcategorias S ON P.subcategoria_fk = S.subcategoria_id;

The fields categoria_fk and subcategoria_fk are fields that store the ID to link between tables.

To view use the fields categoria and subcategoria which are the fields that store the description you want.

Heed

If the product does not have to have a category and subcategory, use the LEFT JOIN instead of INNER JOIN, because if not, products without category or subcategory will not be returned in your select

If you don’t know the types of Join you can take a look here and here

INNER JOIN
This is a common Join format, which returns data only when the two tables have corresponding keys in the ON clause of Join.

LEFT JOIN
It is one of the most used formats of Join, which returns to Table The whole and only the records that match the equality Join in Table B (or null fields for fields without correspondence).