Advanced Firebird Join - I can’t join the tables I need and sort in the right result

Asked

Viewed 114 times

-1

Good evening guys. I’m on a quest that has been depleting my neurons for a week and I still can’t find the solution.

In an ERP used by a client, the demand to visualize purchase value vs sale, grouped by section (market classification. Ex.: Cereals, Laticinios, Matins, Cold and etc).

I can make separate inquiries. One for purchase and one for sale:

Query buys:

SELECT 
A.SECCOD, B.SECDES, SUM(C.ITEVLRTOT) AS COMPRA FROM ITEM_ENTRADA AS C

JOIN 
PRODUTO AS A ON C.PROCOD = A.PROCOD 

JOIN 
SECAO AS B ON A.SECCOD = B.SECCOD 

JOIN 
ENTRADA AS D ON C.ENTDOC = D.ENTDOC
 
WHERE D.ENTDAT BETWEEN '2020-01-01' AND '2020-07-30' GROUP BY A.SECCOD,B.SECDES

Upshot: Resultado Query acima, exibindo valores de compra agrupados por secao

Query Venda

    SELECT A.SECCOD, B.SECDES, SUM (C.ITVVLRTOT) AS VENDA FROM ITEVDA AS C
    
    JOIN 
    PRODUTO AS A ON C.PROCOD = A.PROCOD 
    
    JOIN
    SECAO AS B ON A.SECCOD = B.SECCOD
    
    
    JOIN TRANSACAO AS D ON (D.TRNSEQ = C.TRNSEQ AND D.CXANUM = C.CXANUM AND D.TRNDAT = C.TRNDAT) 
    AND (D.TRNDAT BETWEEN '2020-01-01' AND '2020-01-31')
    AND (D.TRNTIP = '1') AND (C.ITVTIP <> '2') 
    WHERE 1=1 GROUP BY A.SECCOD, B.SECDES ORDER BY 
    A.SECCOD ASC

Upshot: Resultado Query acima, exibindo valores de venda agrupados por secao

What I need is SECCOD, SECDES, TOTAL_COMPRA, TOTAL_VENDA. But I can’t. The closest I got, the date filter was applied either on purchase, or on sale.

Is there any possibility of doing this consultation as I wish?

sql join firebase-database

  • I recommend putting the expected output and SQL on the site http://sqlfiddle.com/ so we can help you better

  • Try Union, see if this helps https://forum.imasters.com.br/topic/586346-select-abstract_em-v%C3%A1rias-tables/? do=findComment&comment=2287737

  • @Motta, I tried with 'UNION ALL', but he puts the results just below the first select. It looks like this: (columns displayed) 'Select1: SECCOD, SECDES, COMPRA' Select2: 'SECCOD, SECDES, VENDA'. I wanted the result like this: 'SECCOD, SECDES, BUY, SALE'

2 answers

0


One of the ways to solve your problem is by using sub-consultations with alias and Join or left Join, depending on the situation, as below:

SELECT COMP.*, VEND.VENDA FROM (
        SELECT A.SECCOD, B.SECDES, SUM(C.ITEVLRTOT) AS COMPRA FROM ITEM_ENTRADA AS C
    
        JOIN PRODUTO AS A ON C.PROCOD = A.PROCOD 
    
        JOIN SECAO AS B ON A.SECCOD = B.SECCOD 
    
        JOIN ENTRADA AS D ON C.ENTDOC = D.ENTDOC
     
        WHERE D.ENTDAT BETWEEN '2020-01-01' AND '2020-07-30' GROUP BY A.SECCOD,B.SECDES
) AS COMP
LEFT JOIN (
        SELECT A.SECCOD, B.SECDES, SUM (C.ITVVLRTOT) AS VENDA FROM ITEVDA AS C
        
        JOIN 
        PRODUTO AS A ON C.PROCOD = A.PROCOD 
        
        JOIN
        SECAO AS B ON A.SECCOD = B.SECCOD
        
        
        JOIN TRANSACAO AS D ON (D.TRNSEQ = C.TRNSEQ AND D.CXANUM = C.CXANUM AND D.TRNDAT = C.TRNDAT) 
        AND (D.TRNDAT BETWEEN '2020-01-01' AND '2020-01-31')
        AND (D.TRNTIP = '1') AND (C.ITVTIP <> '2') 
        WHERE 1=1 GROUP BY A.SECCOD, B.SECDES ORDER BY 
        A.SECCOD ASC
) AS VEND
    ON COMP.SECCOD = VEND.SECCOD
    AND COMP.SECDES = VEND.SECDES

  • thank you so much for helping. As soon as possible, I will conduct the tests and comment on the result here. And to get to the bottom of it, I’m going to study a little bit about subconsultas.

  • solved. That’s what you saw. In the second consultation, I used only the JOIN. The performance was faster and there was no change in the result values. Following the same logic, can I create a subquery to perform an operation? Ex.: (SALE / PURCHASE)

  • If you’re talking about mathematical operations, yes, follow an example: SELECT COMP.*, VEND.VENDA, (VEND.VENDA / COMP.COMPRA) AS DIVISAO FROM (....

  • That’s right. I’m already going to test here. You guys are beasts huh. Do you have any indication of course to learn advanced SQL like this? logical that has the part of the experience also right. But wanted something to give a north.

  • The indication of study material is very subjective, it goes from each one, better you research and find on your own, there is enough. Most of the knowledge in SQL is for any database. In the tangent to Firebird, there is material on his official website on https://firebirdsql.org/, unfortunately it does not seem to have in Portuguese.

  • I found a detail: in the period when there was no purchase in a certain section, the value of the sale is not displayed in it. This is due to the end of the sub-consumption ne, which requires SECCOD of the purchase to be equal to SECCOD of the sale.

  • That’s why I used left Join, with left Join list the purchases, without left Join, will list only the purchases that already have sale. According to what you need, you should use the left or not.

  • how do I then so that even if there are no purchases in the period, be shown the value of sales?

  • Reverse the order of the above query, put the left Join in the shopping query sub.

Show 4 more comments

0

Solution with Join

SELECT SECCOD, SECDES, SUM(COMPRA) AS COMPRA , SUM(VENDA) AS VENDA
FROM
(SELECT 
A.SECCOD, B.SECDES, SUM(C.ITEVLRTOT) AS COMPRA , 0 VENDA
FROM ITEM_ENTRADA AS CJOIN 
PRODUTO AS A ON C.PROCOD = A.PROCOD 
JOIN 
SECAO AS B ON A.SECCOD = B.SECCOD 
JOIN 
ENTRADA AS D ON C.ENTDOC = D.ENTDOC 
WHERE D.ENTDAT BETWEEN '2020-01-01' AND '2020-07-30' GROUP BY A.SECCOD,B.SECDES
UNION ALL
SELECT A.SECCOD, B.SECDES, 0 COMPRA , SUM (C.ITVVLRTOT) AS VENDA 
FROM ITEVDA AS C   
JOIN 
PRODUTO AS A ON C.PROCOD = A.PROCOD     
JOIN
SECAO AS B ON A.SECCOD = B.SECCOD      
JOIN TRANSACAO AS D ON (D.TRNSEQ = C.TRNSEQ AND D.CXANUM = C.CXANUM AND D.TRNDAT = C.TRNDAT) 
AND (D.TRNDAT BETWEEN '2020-01-01' AND '2020-01-31')
AND (D.TRNTIP = '1') AND (C.ITVTIP <> '2') 
WHERE 1=1 GROUP BY A.SECCOD, B.SECDES ORDER BY 
A.SECCOD ASC
) VIRTUAL
GROUP BY SECCOD, SECDES

  • thanks. I liked this one too. I only had to remove the ORDER BY command from the second select. In this way as you suggested, I think it will be easier to get one more column (SALE / (DIVIDED BY) PURCHASE). I believe that just create two more COMPRA1 and VENDA1 queries, repeating the same parameters. This was the best way I could find. Rsrsrs But I think there is a better way to save code. Anyway, thanks again

  • then would have to know well business rules and tables , in general Union is good because it allows to join the case zeroed making another Union for products

Browser other questions tagged sql join firebase-database

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