SQL helps to join 2 selects

Question

SQL helps to join 2 selects

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

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

I need to get everything sold in a certain period (BETWEEN) per item only at the same time I have to get what I had from the item's stock up to the start date.

To separate whether it is input or output is the option d.type

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

asked by anonymous 01.08.2018 / 23:01

2 answers

Place a button on the input side Find phone numbers within a txt file

score 2 · Answer 1

I think this way you'll be able to get 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 more in the SELECT principal, since UNION validates duplicates, but in any case, it does not badly!

Edit I've set the SUM also in the columns that are value "0", otherwise it would cause problems in the query because they are numeric values and are not grouped.

score 1 · Answer 2

You will have to use a Union Select, and for this the two select must have the same number 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