none
SQL consultar multiplos valores e aproximação RRS feed

  • Pergunta

  • Vou contar um pouco do que preciso para ajudar vocês a entenderem aonde preciso de ajuda. Tenho que produzir um sistema web e a modelagem de dados está a seguinte. Tenho uma tabela que contém matérias-primas como por exemplo madeira, prego, tijolo, etc.

    E outra tabela que contém o produto final por exemplo cadeira, mesa que são feitas de matéria-prima. A lógica da modelagem ficou a seguinte.

    TB_materia                   Tb_materia_produto                Tb_produto            

    cod_materia*  1 --->M    cod_materia*                             nm_produto

    nm_materia                   cod_produto*       M<-------1     cod_produto *

    tipo                                qtd_material


     

    Legenda= M igual a muitos, então basicamente 1 para muitos. A * é a chave primária da tabela. Nm=nome

     

    O que eu preciso fazer é o seguinte, o usuário vai digitar as matérias-primas disponíveis que ele tem como por exemplo madeira e prego, vai buscar se existe estas matérias-primas cadastradas (isso eu sei fazer), depois disso, vai mostrar qual/quais produtos podem ser feitos com o material que ele tem. Ai que está, não sei como fazer esta busca com SQL. Talvez a minha modelagem não seja ideal para resolver esse problema. 

    NÃO É UM SISTEMA DE CONTROLE DE ESTOQUE, MAS SIM UM SISTEMA DE SUGESTÃO DO QUE PODE SER FEITO COM O MATERIAL QUE O USUÁRIO INFORMOU NO SISTEMA.

    Agradeço desde já, qualquer ajuda ou sugestão é bem-vinda, até mesmo para mudar a modelagem de dados, pois ainda não fiz o sistema ainda.


    quinta-feira, 18 de fevereiro de 2016 12:39

Respostas

  • create table tb_produto(cod_produto int identity(1,1), nm_produto varchar(30)) create table tb_materia(cod_materia int identity(1,1), nm_materia varchar(30), metrica varchar(30), qtd int) create table tb_materia_produto(cod_produto int, cod_materia int, qtd int) insert into tb_produto select 'casa' insert into tb_produto select 'cadeira' insert into tb_produto select 'cama' insert into tb_produto select 'armário' insert into tb_materia select 'madeira', 'm3', 30 insert into tb_materia select 'prego', 'und', 250 insert into tb_materia select 'tijolos', 'und', 150000 insert into tb_materia select 'cimento', 'g', 25000 insert into tb_materia select 'parafuso', 'und', 350 insert into tb_materia select 'porca', 'und', 330 insert into tb_materia select 'tinta', 'ml', 10000 insert into tb_materia select 'dobradiças', 'und', 20 insert into tb_materia_produto select 1, 3, 10000 insert into tb_materia_produto select 1, 4, 4500 insert into tb_materia_produto select 2, 1, 2 insert into tb_materia_produto select 2, 2, 30 insert into tb_materia_produto select 2, 7, 200 insert into tb_materia_produto select 1, 7, 2000 insert into tb_materia_produto select 3, 1, 3 insert into tb_materia_produto select 3, 5, 50 insert into tb_materia_produto select 3, 6, 50 insert into tb_materia_produto select 3, 7, 1000 insert into tb_materia_produto select 4, 1, 3 insert into tb_materia_produto select 4, 5, 40 insert into tb_materia_produto select 4, 6, 40 insert into tb_materia_produto select 4, 8, 25 select cod_produto, nm_produto, min(qtd_produto) from ( select p.cod_produto, p.nm_produto, m.cod_materia, m.nm_materia, m.qtd/mp.qtd qtd_produto from tb_materia_produto mp join tb_produto p on mp.cod_produto = p.cod_produto join tb_materia m on mp.cod_materia = m.cod_materia

    where m.qtd/mp.qtd > 0 ) T group by cod_produto, nm_produto having (select count(mp1.cod_materia) from tb_materia_produto mp1 where mp1.cod_produto = T.cod_produto) = count(T.cod_materia)

    Apesar de ter colocado uma quantidade na tabela de matéria, não quer dizer que é um controle de estoque, é apenas uma simulação de valores informados.

    Você pode informar os valores no sistema, atualizar nessa tabela e utilizar a mesma lógica.

    A quantidade de produtos possíveis será sempre igual a divisão do componente de menor quantidade pelo necessário dele para o produto, quando houver suficiente de cada componente do produto.

    Att,


    Antero Marques




    • Editado Antero Marques sexta-feira, 19 de fevereiro de 2016 10:35
    • Marcado como Resposta Marcos SJ segunda-feira, 22 de fevereiro de 2016 13:30
    sexta-feira, 19 de fevereiro de 2016 10:28
  • Fernando,

    Certo, você falou de exemplo de elementos químicos, poxa vida que concidência pois um dos meus últimos projetos como Coordenador de Desenvolvimento foi justamente sobre um software para área de controle de qualidade, análises químicos e laboratórios químicos, talvez a minha modelagem pode te ajudar.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta Marcos SJ segunda-feira, 22 de fevereiro de 2016 13:30
    quinta-feira, 18 de fevereiro de 2016 18:36

Todas as Respostas

  • Fernando,

    Para saber qual produto precisar ser feito ou pode ser feito, você tem isso definido na sua modelagem?

    Existe alguma tabela que guarda os componentes de um produto e qual produto será gerado?

    Isso é necessário justamente para você poder definir toda estar cadeia produtiva e componentes do seu produto.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]



    quinta-feira, 18 de fevereiro de 2016 12:42
  • Bom dia Fernando,

    Se eu entendi bem, voce quer saber quais produtos daria para fazer com as materia que serão informadas correto ?

    Tenta assim com sub-select:


    SELECT PROD_MAT.* FROM 
    (
    SELECT P.COD_PRODUTO,P.NM_PRODUTO,COUNT(MP.COD_MATERIA) AS QTD_MATERIA FROM TB_PRODUTO P INNER JOIN TB_MATERIA_PRODUTO MP 
    ON P.COD_PRODUTO = MP.COD_PRODUTO
    GROUP BY P.COD_PRODUTO,NM_PRODUTO
    ) PROD_MAT 
    INNER JOIN 
    (
    SELECT COD_PRODUTO,COUNT(COD_MATERIA) AS QTD_MATERIA FROM TB_MATERIA_PRODUTO WHERE COD_MATERIA IN(3,4,5,6,10) GROUP BY COD_PRODUTO
    )MAT ON PROD_MAT.COD_PRODUTO = MAT.COD_PRODUTO 
    WHERE PROD_MAT.QTD_MATERIA = MAT.QTD_MATERIA

    Ai ali na clausula in voce coloca as materias que seram informadas, o script compara se as materias informadas são suficientes para criar um produto, ou seja o produto só ira aparecer se conter todas as materias dele, voce pode trocar por LEFT JOIN para entender melhor.

    basicamente No primeiro subselect ele conta quantas materias são necessárias para criar determinado produto e no segundo subselect ele conta quantas materias tem para cada produto, depois no WHERE só compara se as quantidades bate.

    Espero que ajude, e claro pode ter diversas formas de fazer essa query...

    Att

    Reginaldo Silva


    quinta-feira, 18 de fevereiro de 2016 13:34
  • Cadastrado no banco de dados? Não, pois estou na fase de projetar o sistema e a modelagem de dados (não iria começar um projeto sem saber exatamente o que preciso e como fazer). mas posso criar o banco e as tabelas com os valores para teste, como estou fazendo aqui.
    quinta-feira, 18 de fevereiro de 2016 13:47
  • Fernando,

    Se você quer saber quais produtos podem ser produzidos com a matéria-prima em estoque, tem que ter a especificação de tipo e quantidade.

    Na tua tabela de matéria-prima, você tem quantidade em estoque ?

    Na tua tabela de produto/matéria-prima, você tem a quantidade necessária por unidade de produto ?


    Antero Marques



    quinta-feira, 18 de fevereiro de 2016 14:46
  • Bem lembrado, acabei de colocar a quantidade de matéria necessária na tb_materia_produto.

    Mas em relação a quantidade de matéria-prima no estoque não creio que seja preciso neste sistema, pois não é um sistema de controle de estoque, mas sim de sugestão de criação do produto com o material disponível. O usuário vai entrar no sistema e vai colocar as matérias-primas que têm e a quantidade, por essas informações devo sugerir pra ele o(s) produto(s) cadastrado(s) no banco de dados que podem ser criados com as matérias-primas que ele informou. Em relação as especificações do tipo do produto como por exemplo madeira de 1.5 metros, 3 metros ou P,M,G de tamanho poderia criar um novo campo de tipo?

    cod_materia    | nm_produto | tipo

    1                        madeira           P  

    2                        madeira            G

    Mas vale lembrar que estou excluindo a possibilidade de juntar os matérias iguais mas de diferentes tipo  para criar um produto. Exemplo criar um portão de tamanho médio, não vale juntar duas madeiras pequenas para criar uma madeira média.

    Por enquanto com as sugestões que você e outras pessoas me deram está assim a modelagem de dados

    TB_materia                   Tb_materia_produto                Tb_produto            

    cod_materia*  1 --->M    cod_materia*                             nm_produto

    nm_materia                   cod_produto*       M<-------1     cod_produto *

    tipo                                qtd_material

    quinta-feira, 18 de fevereiro de 2016 15:26
  • Estou vendo problema em como fazer o sistema enviar vários matérias-primas e quantidade para procurar no banco de dados e reconhecer que com essas materiais pode ou não fazer algum produto cadastrado, pois não sei como fazer essa pesquisa no banco.
    quinta-feira, 18 de fevereiro de 2016 15:40
  • Fernando,

    Então, para você saber o que cada produto necessita primeiro é necessário e obrigatório ter uma tabela específica para cadastrar cada matéria prima.

    Depois você terá a necessidade de criar uma tabela algo similar a produtos, e ai sim, com base nestas duas tabelas você vai criar uma outra chamada por exemplo: ProdutosXMateriaPrima, onde para produzir um produto você vai relacionar todas as matérias primas.

    Neste caso, quando a sua aplicação for buscar um determinado produto ela vai conseguir trazer exatamente as matérias primas necessárias.

    Em relação a quantidade em estoque, isso é interessante e importante, mas eu não vejo que este atributo deve pertencer a matéria prima, na verdade você deve ter uma tabela de Estoque onde a mesma vai possuir um campo que representa o Código da Matéria Prima ou até mesmo do seu produto já produzido.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 18 de fevereiro de 2016 17:50
  • Fernando,

    Não estou falando em começar um projeto sem saber exatamente o que preciso fazer, a modelagem como você mesmo sabe é sim uma ideia/necessidade do que queremos e do que precisamos, mas para entender esta modelagem ou até mesmo elaborar é necessário entender como as coisas acontecem no dia-á-dia, como eu sempre falo para meus alunos, temos que conhecer os processos da empresa.

    Desta forma, você vai poder rascunhar ou até mesmo desenhar algo diretamente no sua modelagem de como os produtos, matérias - primas e novoso produtos gerados pele beneficiamento de outros são criados, armazenados, controlados e estocados.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    quinta-feira, 18 de fevereiro de 2016 17:53
  • Obrigado Junior Galvão pela atenção.

    Tenho modelada a tabela de matéria prima, produto e também materiaPrimaXproduto. Não coloquei a quantidade de matéria na tabela tb_matéria e sim na tabela MateriaPrimaXProduto, pois creio que seria bom saber a quantidade da matéria prima que iria precisar para fazer o determinado produto. Assim está a modelagem por enquanto.

    TB_materia                   Tb_materia_produto                Tb_produto            

    cod_materia*  1 --->M    cod_materia*                             nm_produto

    nm_materia                   cod_produto*       M<-------1     cod_produto *

    tipo                                qtd_material

    Não é um sistema de controle de estoque, por isso não preciso armazenar quantidade de matéria no estabelecimento, é sim um sistema de sugestão do que fazer com o material que tenho, o usuário entra com o material que ele tem, ou seja, ele já sabe quais matérias ele tem e a quantidade exata, não precisa armazenar um estoque dele.  Puxando exemplo de química (não sei se é um bom exemplo para ilustrar), o usuário informa que possuí 1 elemento de carbono "C" e dois de oxigênio "O2". Então o sistema iria verificar qual substância poderia ser feita a partir desses elementos no banco de dados e retornaria gás carbônico "CO2" se tivesse cadastrado é claro. É um sistema acadêmico que preciso fazer e não de empresa, mas obrigatoriamente preciso seguir o raciocínio de tenho materiais ou elementos químicos, quais produtos ou substâncias posso fazer com isso?

    Poderia estar fazendo o sistema do exemplo de elementos químicos, mas preferi optar por esse de construção.

    O que eu não sei é como fazer esse select que me retornaria o nome(s) dos produtos que poderiam ser criados com os valores (materiais) informados.



    quinta-feira, 18 de fevereiro de 2016 18:30
  • Fernando,

    Certo, você falou de exemplo de elementos químicos, poxa vida que concidência pois um dos meus últimos projetos como Coordenador de Desenvolvimento foi justamente sobre um software para área de controle de qualidade, análises químicos e laboratórios químicos, talvez a minha modelagem pode te ajudar.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta Marcos SJ segunda-feira, 22 de fevereiro de 2016 13:30
    quinta-feira, 18 de fevereiro de 2016 18:36
  • create table tb_produto(cod_produto int identity(1,1), nm_produto varchar(30)) create table tb_materia(cod_materia int identity(1,1), nm_materia varchar(30), metrica varchar(30), qtd int) create table tb_materia_produto(cod_produto int, cod_materia int, qtd int) insert into tb_produto select 'casa' insert into tb_produto select 'cadeira' insert into tb_produto select 'cama' insert into tb_produto select 'armário' insert into tb_materia select 'madeira', 'm3', 30 insert into tb_materia select 'prego', 'und', 250 insert into tb_materia select 'tijolos', 'und', 150000 insert into tb_materia select 'cimento', 'g', 25000 insert into tb_materia select 'parafuso', 'und', 350 insert into tb_materia select 'porca', 'und', 330 insert into tb_materia select 'tinta', 'ml', 10000 insert into tb_materia select 'dobradiças', 'und', 20 insert into tb_materia_produto select 1, 3, 10000 insert into tb_materia_produto select 1, 4, 4500 insert into tb_materia_produto select 2, 1, 2 insert into tb_materia_produto select 2, 2, 30 insert into tb_materia_produto select 2, 7, 200 insert into tb_materia_produto select 1, 7, 2000 insert into tb_materia_produto select 3, 1, 3 insert into tb_materia_produto select 3, 5, 50 insert into tb_materia_produto select 3, 6, 50 insert into tb_materia_produto select 3, 7, 1000 insert into tb_materia_produto select 4, 1, 3 insert into tb_materia_produto select 4, 5, 40 insert into tb_materia_produto select 4, 6, 40 insert into tb_materia_produto select 4, 8, 25 select cod_produto, nm_produto, min(qtd_produto) from ( select p.cod_produto, p.nm_produto, m.cod_materia, m.nm_materia, m.qtd/mp.qtd qtd_produto from tb_materia_produto mp join tb_produto p on mp.cod_produto = p.cod_produto join tb_materia m on mp.cod_materia = m.cod_materia

    where m.qtd/mp.qtd > 0 ) T group by cod_produto, nm_produto having (select count(mp1.cod_materia) from tb_materia_produto mp1 where mp1.cod_produto = T.cod_produto) = count(T.cod_materia)

    Apesar de ter colocado uma quantidade na tabela de matéria, não quer dizer que é um controle de estoque, é apenas uma simulação de valores informados.

    Você pode informar os valores no sistema, atualizar nessa tabela e utilizar a mesma lógica.

    A quantidade de produtos possíveis será sempre igual a divisão do componente de menor quantidade pelo necessário dele para o produto, quando houver suficiente de cada componente do produto.

    Att,


    Antero Marques




    • Editado Antero Marques sexta-feira, 19 de fevereiro de 2016 10:35
    • Marcado como Resposta Marcos SJ segunda-feira, 22 de fevereiro de 2016 13:30
    sexta-feira, 19 de fevereiro de 2016 10:28
  • Bom dia,

    Por falta de retorno do usuário, esta thread será encerrada.

    Caso seja necessário, por gentileza, abra uma thread nova.

    Atenciosamente

    Marcos SJ

    Esse conteúdo e fornecido sem garantias de qualquer tipo, seja expressa ou implícita

    MSDN Community Support

    Por favor, lembre-se de Marcar como Resposta as postagens que resolveram o seu problema. Essa é uma maneira comum de reconhecer aqueles que o ajudaram e fazer com que seja mais fácil para os outros visitantes encontrarem a resolução mais tarde.

    segunda-feira, 22 de fevereiro de 2016 13:30