Select connecting 3 tables in Firebird with different IDs

Navigation
0

Good afternoon,

I need to make a select that returns the 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_PRODUCT).

However, the IDs of the records in the two tables do not match to make a direct JOIN because the customer has deleted items from the stock. There is a connection table (TB_EST_IDENTIFIER) between ID's ID_EST and ID_IDENTIFIER.

ThecodeIwrotesofar,wasquotedbelow.ButitisreturningtheitemsbymatchingtheIDs,joiningtherecordsofthetablesthathavetheID_ESTOQUEandID_IDENTIFIERequalinarow.

SELECTprod.id_identificador,est.id_estoque,prod.cod_barra,est.DESCRICAOasDESCRICAO,est.PRC_VENDAasPRC_VENDAFROM(TB_EST_PRODUTOprodJOINTB_ESTOQUEestONprod.ID_IDENTIFICADOR=est.ID_ESTOQUE)JOINtb_est_identificadoridentONprod.id_identificador=ident.id_identificador

Thesystemscreenisthis:

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

    
asked by anonymous 07.07.2017 / 21:48

1 answer

0

I got the code below. What was missing was to specify the JOIN of table TB_EST_PRODUCT 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
    
08.07.2017 / 05:05
Resize image according to device size Determine the largest prime number after being given a value using while function [duplicate]