How to relate these tables, returning specific values?

Asked

Viewed 74 times

0

[Firebird] sgbd - Ibexpert

I need to know how to relate these two tables (comment below), so that I get the following results:


TABLE:CONTARECEBER

  • DTDOCUMENTO (date entered the system)
  • MATURITY (date the document will expire)
  • DTCANCELLATION (date of cancellation of the document)
  • STCANCELED (cancelled document status)
  • CODCONTARECEBER (document code)

TABLE: CONTARECEBERQUITACAO

  • CODCONTARECEBERQUITACAO (document discharge code, if any)
  • DTQUITACAO (Date the document was completed, if any)

These, are the ones I need to display, they are in two tables, I tried to use LEFT JOIN on my command, but it’s not working, I thought I could be doing something wrong too.

select 
    contareceber.dtdocumento,
    contareceber.dtvencimento,
    contareceber.dtcancelamento,
    contareceber.stcancelada,
    contareceber.codcontareceber,
    contareceber.codorganizacao,
    contareceberquitacao.dtquitacao,
    contareceberquitacao.codcontareceberquitacao
from contareceber
   left join contareceberquitacao on (contareceber.codcontareceberquitacao = contareceberquitacao.codcontareceberquitacao)
where (contareceber.dtdocumento between '01.01.2015' and '31.12.2015') and (contareceber.codorganizacao = '001')
  • 1

    what is the manager: Oracle Database? Mariadb? SQL Server? other? // How is dtdocument column declared?

  • 1

    (1) comma missing after contareceberquitacao.dtquitacao, on the eighth line // (2) The junction should be contareceber.codcontareceber = contareceberquitacao.codcontareceberquitacao

  • codcontareceber is not the same as codcontareceberdischarge, are distinct ID’s.

  • So, how to establish the relationship between the two tables? It is necessary to have at least one column in common. That is, in the CONTARECEBERQUITACAO table which column contains the document code?

  • Thank you! that was just the problem, it has the same field, the two tables have the CODCONTARECEBERQUITACAO, but in the table CONTARECEBE is going to zero, now I know that the problem is when to save the fields... as it is not filled it does not find... THANKS anyway !

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.