I'm trying to put together 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. But this discount was registered for the product categories, and within each category I have several families of products, so within each family I finally have the products.
In this way I put up a SQL that looks 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 gave me the following result.
| NUMERONF | Contact Us | - | SEQPRODUCT |
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 that is the PERCENTAGE of discount that each product had upon entering.
But my difficulty comes now ...
I have 3 tables that contains the contract information.
- MGC_CONTRACT - It tells me which is the provider of this contract (I need to add it in the query to be able to associate the contract provider with the note provider, but this is easy)
- MGC_CONTRATODESCONTO - It tells me what 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 tied to its respective discount.
So for example,
- I have a contract (SEQONTRACT) number 10 Within this SEQUENCE Number 10 I have five different percentages (SEQDESCONTO) 1%, 2%, 3%, 4% and 5% (each with a different SEQDESCONTO)
- And within each SEQDESCONTACT I have linked categories.
Now I need to know how to put in my SQL the percentage of this particular product.
Being that the percentage was registered in the category ...
Within the category I have several families (these two information I have in the MAP_FAMDIVCATEG table)
Within each family I have the products (I have this inside the MAP_PRODUCT table)