Add column to SQL considering some conditions to be valid

Question

I’m trying to gather two pieces of information and I’m not getting it.

I set up this SQL to bring me only the products that were purchased that had a discount through a contract. However this discount was registered for the product categories, and within each category I have several product families, just within each family I finally have the products.

This way I set up an SQL that looked like this.

SELECT A.NUMERONF, A.NROEMPRESA, A.SEQPRODUTO FROM MLF_NFITEM A WHERE A.SEQPRODUTO IN (SELECT SEQPRODUTO FROM MAP_PRODUTO WHERE SEQFAMILIA IN (SELECT SEQFAMILIA FROM MAP_FAMDIVCATEG WHERE SEQCATEGORIA IN (SELECT SEQCATEGORIA FROM MGC_CONTRATOCATEGORIA)));

Assuming this SQL has generated me the following result.

|NUMERONF| - |NROEMPRESA| - |SEQPRODUTO|

525 - 3 - 25085
525 - 3 - 25087
525 - 3 - 25088
525 - 3 - 25095
525 - 3 - 25097
525 - 3 - 25099
525 - 3 - 25105

Now I need to add a column in this SQL which is the discount PERCENTAGE that each product has had on input.

But my difficulty comes now...

I have 3 tables that contain the contract information.

• MGC_CONTRATO - It tells me which is the supplier of this contract (I need to add it in consultation to be able to associate the supplier of the contract with the supplier of the note, but this is easy)
• MGC_CONTRATODESCONTO - It tells me which discount percentage was registered (Detail, within a contract I can have more than one discount percentage, each for one or more categories)
• MGC_CONTRATOCATEGORIA - It tells me which categories are linked to your respective discount.

So for example,

• I have a contract (SEQCONTRATO) number 10
• Within this SEQCONTRACT number 10 I have five different percentages (SEQDISCOUNT) 1%, 2%, 3%, 4% and 5% (each with a different SEQDISCOUNT)
• And within each SEQDESCONTO I have the categories linked.

Now I need to know how to put in my SQL the percentage of this particular product.
And the percentage was registered in the category...
Within the category I have several families (this two information I have in the table MAP_FAMDIVCATEG)
Within each family I have the products (I have it within the table MAP_PRODUTO)