Select connecting 3 tables in Firebird with different Ids

Question

Good afternoon,

I need to make a select that returns to description of the product,barcode and price.

The description and price are in one table (TB_ESTOQUE) and the barcode is in another (TB_EST_PRODUTO).

However, the ID s of the records in the two tables do not match to make a direct JOIN, because the client excluded items from the stock. There is a connection table (TB_EST_IDENTIFICADOR) between is ID s ID_ESTOQUE and ID_IDENTIFICADOR.

The code I wrote so far was quoted below. Only that it is returning the items matching the Ids, joining the records of the tables that have the ID_ESTOQUE and ID_IDENTIFICADOR equal in a row.

SELECT
    prod.id_identificador,
    est.id_estoque,
    prod.cod_barra,
    est.DESCRICAO as DESCRICAO,
    est.PRC_VENDA as PRC_VENDA
FROM
    ( TB_EST_PRODUTO prod
JOIN
    TB_ESTOQUE est ON prod.ID_IDENTIFICADOR = est.ID_ESTOQUE)
JOIN
    tb_est_identificador ident ON prod.id_identificador = ident.id_identificador

The system screen is this:

Ai I need select to return these items as the system shows.

Answer 1

I managed with the code below. What was missing was to specify the JOIN of the table TB_EST_PRODUTO as LEFT.

select
    ident.id_identificador,
    est.id_estoque,
    est.descricao,
    est.prc_venda,
    prod.cod_barra
from tb_estoque est
    inner join tb_est_identificador ident
        on est.id_estoque = ident.id_estoque
    left join tb_est_produto prod
        on ident.id_identificador = prod.id_identificador