Sql

[Shakapp 0]

Amigos preciso de uma ajuda para o sql abaixo, não estou conseguindo unir as informações dos produtos vendidos e não vendidos. Obrigado!

 

/*TABELAS TEMPORARIAS APENAS PARA TESTE*/
CREATE TABLE CLIENTE(/*TABELA DE CLIENTE*/
   CLI_COD INT
);
INSERT INTO CLIENTE VALUES(1);
INSERT INTO CLIENTE VALUES(2);

CREATE TABLE PRODUTO(/*TABELA DE PRODUTO*/
   COD_PROD INT
);
INSERT INTO PRODUTO VALUES(1);
INSERT INTO PRODUTO VALUES(2);
INSERT INTO PRODUTO VALUES(3);
INSERT INTO PRODUTO VALUES(4);
INSERT INTO PRODUTO VALUES(5);
INSERT INTO PRODUTO VALUES(6);

CREATE TABLE NOTA_FISCAL(/*TABELA DE NOTA FISCAL, ONDE POSSO VER OS PRODUTOS VENDIDOS PARA UM DETERMINADO CLIENTE*/
   NF_COD INT,
   CLI_COD INT,
   COD_PROD INT
);
INSERT INTO NOTA_FISCAL VALUES(1,1,1);
INSERT INTO NOTA_FISCAL VALUES(1,1,2);
INSERT INTO NOTA_FISCAL VALUES(1,1,3);
INSERT INTO NOTA_FISCAL VALUES(2,2,1);
INSERT INTO NOTA_FISCAL VALUES(2,2,3);
INSERT INTO NOTA_FISCAL VALUES(2,2,4);/*ESTE ITEM NÃO ENTRA NO SELECT POIS ELE ESTÁ NO GRUPO 2 E O SELECT PEGA APENAS PRODUTO DO GRUPO 1*/

CREATE TABLE GRUPO_PRODUTO(/*GRUPO DE PRODUTOS*/
   GRU_COD INT,
   COD_PROD INT
);
INSERT INTO GRUPO_PRODUTO VALUES(1,1);
INSERT INTO GRUPO_PRODUTO VALUES(1,2);
INSERT INTO GRUPO_PRODUTO VALUES(1,3);
INSERT INTO GRUPO_PRODUTO VALUES(2,4);
INSERT INTO GRUPO_PRODUTO VALUES(2,5);
INSERT INTO GRUPO_PRODUTO VALUES(2,6);

/*BUSCAR QUAIS PRODUTOS DE UM GRUPO FORAM OU NÃO FORAM VENDIDOS PARA O CLIENTE*/

SELECT CLI.CLI_COD,GRU.COD_PROD,COUNT(NF.COD_PROD) CONT/*PRODUTOS VENDIDOS, APENAS CONSEGUI MONTAR O SELECT QUE TRAS QUAIS PRODUTOS FORAM VENDDIDOS, JÁ TENTEI UNION LEFT E NAO CONSIGO UNIR OS VENDIDOS COM OS NÃO VENDIDOS*/
FROM CLIENTE CLI 
    RIGHT OUTER JOIN NOTA_FISCAL NF ON CLI.CLI_COD=NF.CLI_COD
    RIGHT OUTER JOIN GRUPO_PRODUTO GRU ON GRU.COD_PROD=NF.COD_PROD
WHERE GRU.GRU_COD=1
GROUP BY CLI.CLI_COD,GRU.COD_PROD

/*RETORNO */
/*|CLI_COD|COD_PROD|CONT|*/
/*|      1|       1|   1|*/
/*|      1|       2|   1|*/
/*|      1|       3|   1|*/
/*|      2|       1|   1|*/
/*|      2|       3|   1|*/

/*PRECISO QUE RETORNE*/
/*|CLI_COD|COD_PROD|CONT|*/
/*|      1|       1|   1|*/
/*|      1|       2|   1|*/
/*|      1|       3|   1|*/
/*|      2|       1|   1|*/
/*|      2|       2|   0|  LINHA QUE FALTA INDICANDO QUE PARA O PRODUTO 2 NO CLIENTE 2 NÃO HOUVE VENDA*/
/*|      2|       3|   1|*/

[Motta 645]

Creio que terá de usar outer joins.

[Shakapp 0]

Então amigo tentei desta forma mas o resultado é o mesmo.

SELECT CLI.CLI_COD,GRU.COD_PROD,COUNT(NF.COD_PROD) CONT
FROM CLIENTE CLI 
    LEFT OUTER JOIN NOTA_FISCAL NF ON CLI.CLI_COD=NF.CLI_COD
    LEFT OUTER JOIN GRUPO_PRODUTO GRU ON GRU.COD_PROD=NF.COD_PROD
WHERE GRU.GRU_COD=1
GROUP BY CLI.CLI_COD,GRU.COD_PROD

 

SELECT CLI.CLI_COD,GRU.COD_PROD,COUNT(NF.COD_PROD) CONT
FROM CLIENTE CLI 
    RIGHT OUTER JOIN NOTA_FISCAL NF ON CLI.CLI_COD=NF.CLI_COD
    RIGHT OUTER JOIN GRUPO_PRODUTO GRU ON GRU.COD_PROD=NF.COD_PROD
WHERE GRU.GRU_COD=1
GROUP BY CLI.CLI_COD,GRU.COD_PROD

[Motta 645]

Em tese a 1º deveria funcionar, mas não conheço as regras de outer join do Mysql à fundo.

[DiegoAngra07 21]

Boa tarde amigo,

 

Olha, eu rodei suas querys aqui pra criar o banco, dei uma mexida e fiz umas tentativas.

 

Eu acredito que ele não esteja te trazendo justo porque na tabela GRUPO_PRODUTO não existe um registro que relacione o cliente 2 com produto 2. Sendo assim, seja qual for o JOIN, não traz aqui ali que você quer. No máximo ele poderia trazer uma das colunas com 2 e a outra preenchida como nulo (pois a relação não existe).

 

O que você precisa fazer exatamente? Mostrar cada cliente x produto e a quantidade comprada?

 

O cliente Diego comprou 3 produtos, 5 unidades de cada. Logo haverão 3 linhas do cliente Diego no teu retorno. Porém ele não comprou um produto X, então você precisa de uma linha que diga essa informação. Mais ou menos isso?

 

Podemos tentar achar outra maneira de fazer. Com JOINs, somente se você cruzar todos os clientes com produtos, e a contagem ser feita de forma separada.

 

Acho que foi isso pelo que eu entendi, perdão se estiver errado. A disposição, abraço.

[Motta 645]

Tente fazer uma tabela virtual de NOTA_FISCAL NF com GRUPO_PRODUTO e "outerjoinhar" esta com CLIENTE.

 

Todo produto de NOTA_FISCAL NF existe em GRUPO_PRODUTO, não ?

[Shakapp 0]

Tente fazer uma tabela virtual de NOTA_FISCAL NF com GRUPO_PRODUTO e "outerjoinhar" esta com CLIENTE.

 

Todo produto de NOTA_FISCAL NF existe em GRUPO_PRODUTO, não ?

Vou tentar, sim toda NF tem o produto que esta ligado a um grupo.

 

O que eu preciso fazer se cada cliente comprou ou nao um determinado produto de um grupo, o count estou fazendo apenas para depois substituir por um S ou N, ou seja depois vou colocar um case onde se o count >0 colocar 'S' se não colocar 'N', não preciso devolver a quantidade vendida.

 

Se vou analisar o grupo 1, e nele tem os produtos 1,2,3 no resultado todo cliente que tem NF emitida deve voltar o resultado dizendo se ele comprou ou não aqueles produtos daquele grupo.

 

Boa tarde amigo,

 

Olha, eu rodei suas querys aqui pra criar o banco, dei uma mexida e fiz umas tentativas.

 

Eu acredito que ele não esteja te trazendo justo porque na tabela GRUPO_PRODUTO não existe um registro que relacione o cliente 2 com produto 2. Sendo assim, seja qual for o JOIN, não traz aqui ali que você quer. No máximo ele poderia trazer uma das colunas com 2 e a outra preenchida como nulo (pois a relação não existe).

 

O que você precisa fazer exatamente? Mostrar cada cliente x produto e a quantidade comprada?

 

O cliente Diego comprou 3 produtos, 5 unidades de cada. Logo haverão 3 linhas do cliente Diego no teu retorno. Porém ele não comprou um produto X, então você precisa de uma linha que diga essa informação. Mais ou menos isso?

 

Podemos tentar achar outra maneira de fazer. Com JOINs, somente se você cruzar todos os clientes com produtos, e a contagem ser feita de forma separada.

 

Acho que foi isso pelo que eu entendi, perdão se estiver errado. A disposição, abraço.

 

A ligação seria, Cliente->Nota_Fiscal->Grupo_produto

O cliente liga a nota_fiscal pelo cli_cod, a nota liga ao grupo produto pelo cod_prod

Na verdade nem preciso da tabela produto, apenas as tabelas Cliente,Nota_fiscal,Grupo_produto

[DiegoAngra07 21]

Entendi.

 

Cara, complicado pelo que eu to pensando aqui. Fazer os vendidos é fácil. Tu não pode resolver esta situação via aplicação? Quem sabe seria mais simples. Ou então criar uma Procedure. São alternativas a se pensar.

[Motta 645]

NF X PRODUTOS

 

SELECT NF.COD_PROD,GRU.COD_PROD
FROM NOTA_FISCAL NF,GRUPO_PRODUTO GRU
WHERE GRU.COD_PROD=NF.COD_PROD 

 

CLIENTE X (NF X PRODUTOS)

 

SELECT CLI.CLI_COD,VIRTUAL.COD_PROD,COUNT(VIRTUAL.COD_PROD) CONT
FROM CLIENTE CLI,
    RIGTH OUTER JOIN
    (SELECT NF.COD_PROD,GRU.COD_PROD
     FROM NOTA_FISCAL NF,GRUPO_PRODUTO GRU
     WHERE GRU.COD_PROD=NF.COD_PROD ) VIRTUAL
     ON CLI.CLI_COD=VIRTUAL.CLI_COD
WHERE VIRTUAL.GRU_COD=1

 

Deve precisar de algum ajuste, creio.

[Shakapp 0]

NF X PRODUTOS

 

SELECT NF.COD_PROD,GRU.COD_PROD
FROM NOTA_FISCAL NF,GRUPO_PRODUTO GRU
WHERE GRU.COD_PROD=NF.COD_PROD 

 

CLIENTE X (NF X PRODUTOS)

 

SELECT CLI.CLI_COD,VIRTUAL.COD_PROD,COUNT(VIRTUAL.COD_PROD) CONT
FROM CLIENTE CLI,
    RIGTH OUTER JOIN
    (SELECT NF.COD_PROD,GRU.COD_PROD
     FROM NOTA_FISCAL NF,GRUPO_PRODUTO GRU
     WHERE GRU.COD_PROD=NF.COD_PROD ) VIRTUAL
     ON CLI.CLI_COD=VIRTUAL.CLI_COD
WHERE VIRTUAL.GRU_COD=1

 

Deve precisar de algum ajuste, creio.

 

Fiz o seguinte, ajustei pois a virtual estava devolvendo 2 cod_prod, e não estava devolvendo o cli_cod e gru_cod que é usado em comparações abaixo depois que ela é criada, ficou assim

 

SELECT CLI.CLI_COD,VIRT.COD_PROD,COUNT(VIRT.COD_PROD) CONT
FROM #CLIENTE CLI RIGHT OUTER JOIN (SELECT NF.CLI_COD,GRU.COD_PROD,GRU.GRU_COD
     FROM #NOTA_FISCAL NF,#GRUPO_PRODUTO GRU
     WHERE GRU.COD_PROD=NF.COD_PROD ) VIRT
     ON CLI.CLI_COD=VIRT.CLI_COD
WHERE VIRT.GRU_COD=1
GROUP BY  CLI.CLI_COD,VIRT.COD_PROD

O resultado foi o mesmo do que eu apresentei acima, a linha com o produto 2 e o cont com 0 não foi exibido da mesma forma.

 

Infelizmente não deu certo.

 

Desta forma consigo listar todas as combinações possivel mas não consigo realizar o cont para ver qual é 0 e qual não é.

 

SELECT COB.CLI_COD,GRU.COD_PROD
FROM (SELECT NF.CLI_COD,NF.COD_PROD
     FROM NOTA_FISCAL NF
          INNER JOIN GRUPO_PRODUTO GRU ON GRU.COD_PROD=NF.COD_PROD
     WHERE GRU.GRU_COD=1
     GROUP BY  NF.CLI_COD,NF.COD_PROD)COB
,GRUPO_PRODUTO GRU     
WHERE GRU.GRU_COD=1
GROUP BY COB.CLI_COD,GRU.COD_PROD

 

Caraca olha a Gambiarra para dar certo, pelo menos ta retornando oque eu quero, vou fazer mais testes para ver se isso resolve hehe

 

SELECT TODOS.CLI_COD,TODOS.COD_PROD,CASE WHEN COB.CONT IS NULL THEN 'N' ELSE 'S' END VENDIDO
FROM
(SELECT NF.CLI_COD,NF.COD_PROD,COUNT(DISTINCT NF.COD_PROD) CONT
     FROM NOTA_FISCAL NF
         INNER JOIN GRUPO_PRODUTO GRU ON GRU.COD_PROD=NF.COD_PROD
     WHERE GRU.GRU_COD=1
     GROUP BY  NF.CLI_COD,NF.COD_PROD)COB
RIGHT OUTER JOIN
(SELECT COB.CLI_COD,GRU.COD_PROD
FROM (SELECT NF.CLI_COD,NF.COD_PROD
     FROM NOTA_FISCAL NF          
         INNER JOIN GRUPO_PRODUTO GRU ON GRU.COD_PROD=NF.COD_PROD
     WHERE GRU.GRU_COD=1
     GROUP BY  NF.CLI_COD,NF.COD_PROD)COB
,GRUPO_PRODUTO GRU     
WHERE GRU.GRU_COD=1)TODOS ON TODOS.CLI_COD=COB.CLI_COD AND TODOS.COD_PROD=COB.COD_PROD
GROUP BY TODOS.CLI_COD,TODOS.COD_PROD

[Motta 645]

Tente

 

SELECT CLI.CLI_COD,VIRTUAL.COD_PROD,COUNT(VIRTUAL.COD_PROD) CONT
FROM CLIENTE CLI,
    RIGTH OUTER JOIN
    (SELECT DISTINCT NF.COD_PROD,GRU.COD_PROD
     FROM NOTA_FISCAL NF,GRUPO_PRODUTO GRU
     WHERE GRU.COD_PROD=NF.COD_PROD ) VIRTUAL
     ON CLI.CLI_COD=VIRTUAL.CLI_COD
WHERE VIRTUAL.GRU_COD=1