SQL helps to join 2 selects

Asked

Viewed 267 times

1

I have 2 selects below, how can I join in only 1 select?

I need to take everything that sold in a certain period (BETWEEN) by item only that at the same time I have to take what had stock of the item up to the initial date.

to separate whether it is input or output is d.type option

Example:

Produto     Estoque             qtd vendida
XPTO         100                     50

Select 1: 

SELECT DISTINCT d.cat_id prod, p.id, p.nome id_prod, 
SUM( d.qtd ) AS qtd_prod, SUM( d.valor_total ) AS total_prod 
FROM lc_controle c 
INNER JOIN lc_detalhe d ON d.controle_id = c.id 
INNER JOIN `lc_cat` p ON p.id = d.cat_id 
WHERE d.tipo = '0' 
and c.dtConcat BETWEEN '$dtinicial' AND '$dtfinal' 
and c.exportado = 'Sim' 
GROUP BY d.cat_id 
ORDER BY qtd_prod DESC

Select 2:

SELECT DISTINCT d.cat_id prod, p.id, p.nome id_prod, 
SUM( d.qtd ) AS qtd_estoque
FROM lc_controle c 
INNER JOIN lc_detalhe d ON d.controle_id = c.id 
INNER JOIN `lc_cat` p ON p.id = d.cat_id 
WHERE d.tipo = '0' 
and c.dtConcat < '$dtinicial' 
and c.exportado = 'Sim' 
GROUP BY d.cat_id 
ORDER BY qtd_estoque DESC
mysql sql mysqli

2 answers

2

I believe that in this way you will achieve the results you want:

SELECT      DISTINCT TMP.*
FROM        (
                SELECT      DISTINCT d.cat_id   AS prod
                        ,   p.id
                        ,   p.nome              AS id_prod
                        ,   SUM(d.qtd)          AS qtd_prod
                        ,   SUM(d.valor_total)  AS total_prod
                        ,   SUM(0)              AS qtd_estoque
                FROM        lc_controle c 
                INNER JOIN  lc_detalhe  d ON d.controle_id  = c.id 
                INNER JOIN  lc_cat      p ON p.id           = d.cat_id 
                WHERE       d.tipo      = '0' 
                        AND c.dtConcat  BETWEEN '$dtinicial' AND '$dtfinal' 
                        AND c.exportado = 'Sim' 
                GROUP BY    d.cat_id 
                UNION
                SELECT      DISTINCT d.cat_id   AS prod
                        ,   p.id
                        ,   p.nome              AS id_prod
                        ,   SUM(0)              AS qtd_prod
                        ,   SUM(0)              AS total_prod
                        ,   SUM(d.qtd)          AS qtd_estoque
                FROM        lc_controle c 
                INNER JOIN  lc_detalhe  d ON d.controle_id  = c.id 
                INNER JOIN  lc_cat      p ON p.id           = d.cat_id 
                WHERE       d.tipo      = '0' 
                        AND c.dtConcat  < '$dtinicial' 
                        AND c.exportado = 'Sim' 
                GROUP BY    d.cat_id
            ) TMP
ORDER BY    TMP.qtd_prod DESC

In principle the DISTINCT will be the most in SELECT main, since the UNION validates duplicates, but anyway, it doesn’t hurt!

Edit
I put the SUM also in columns with value "0", otherwise it would cause problems in the query because they are numerical values and are not grouped.

  • John, thanks for the help...but the second part he is not adding up the quantities and is bringing all the items and not just the items that have movement of the first part......

  • The SUM is not being done in the 2nd SELECT? Strange... if you run each query individually, what results have?

  • If I turn only the first part the result is the same with the second part, the problem is in the second part, I manually added the Qtd of the first part and is bringing the correct quantities...I have an item that it shows in the first part and I had output in the reported period of 426 units and input until the initial date that is in the second part of 600 units and it shows zero

  • Attention that in the second consultation he will add only quantities prior to the initial date! I do not know if you are taking this into account.

  • yes that’s just what I need, that item I told you his input was before the initial date so it should have added

  • Are you sure that the SELECT who put in his question return data? Using the INNER JOIN the table lc_control must have records in the lc_detail and this in lc_cat. Confirm pf the information

  • Yes return the information only that separate, had a query for entries and other for exits, I need to unify precisely to have the 2 information in the same report, so: the user chooses the initial date and final date for consultation the report brings all the items that had movement in this period as follows: Name of the product quantity sold input quantity and total value of sale

  • Let’s go continue this discussion in chat.

Show 3 more comments

1

You will have to use a Union Select, and for that the two select have to have the same amount and the same names of return fields;

 Select prod, id_prod, qtd_prod, total_prod from ((
      SELECT DISTINCT d.cat_id prod, p.id, p.nome id_prod, 
      SUM( d.qtd ) AS qtd_prod, SUM( d.valor_total ) AS total_prod 
      FROM lc_controle c 
      INNER JOIN lc_detalhe d ON d.controle_id = c.id 
      INNER JOIN `lc_cat` p ON p.id = d.cat_id 
      WHERE d.tipo = '0'
    ) as t1 
    union select (
      SELECT DISTINCT d.cat_id prod, p.id, p.nome id_prod, 
      SUM( d.qtd ) AS qtd_estoque, 0 as total_prod 
      FROM lc_controle c 
      INNER JOIN lc_detalhe d ON d.controle_id = c.id 
      INNER JOIN `lc_cat` p ON p.id = d.cat_id 
      WHERE d.tipo = '0' 
      and c.dtConcat < '$dtinicial' 
      and c.exportado = 'Sim' 
      GROUP BY d.cat_id 
      ORDER BY qtd_estoque DESC
    ) as t2
) as t3

reference : mysql

  • I rushed here, but adapting your needs should address.

  • 1

    gave error, pq vc took the initial and final date in BETWEEN, do not know much of select......#1064 - You have a syntax error in your SQL next to 'Union select ( SELECT DISTINCT d.cat_id Prod, p.id, p.id_prod name, ' on line 9

Browser other questions tagged mysql sql mysqli

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