Comando sql para procurar conjunto de valores e aproximação

[fernandochristhian]

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

 

Parte 1 -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, ou o problema não seja só no SQL.

 

Parte 2- Na parte dois queria fazer algo por aproximação, para fazer uma porta precisa de (madeira, dobradiça, cadeado), o usuário digitou madeira e dobradiça, queria mostrar olha se tu quiser fazer uma porta ainda vai precisar ainda do cadeado, mas essa segunda talvez eu consiga me virar desde que eu saiba como fazer a primeira parte que eu não sei.

 

Não é um sistema de controle de estoque que me obrigaram a fazer.

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, estou projetando e me deparei que a pesquisa da primeira parte e segunda não sei como fazer.

 

 

[emailpublico3]

Boa noite,

Então, como não houveram respostas, vou arriscar propondo uma solução.

Modelei aqui, e a ideia que proponho é você obter da lista do que compõe a maior quantidade de itens para um determinado produto. Modelei e abaixo segue script para criação, popular e um exemplo do que pensei.

 

 

 

 

CRIANDO TABELAS

 

CREATE TABLE produto (
                codProduto INT AUTO_INCREMENT NOT NULL,
                nomeProduto VARCHAR(100) NOT NULL,
                PRIMARY KEY (codProduto)
);


CREATE TABLE materia (
                codMateria INT AUTO_INCREMENT NOT NULL,
                nomeMateria VARCHAR(20) NOT NULL,
                PRIMARY KEY (codMateria)
);


CREATE TABLE materia_produto (
                codProduto INT NOT NULL,
                codMateria INT NOT NULL,
                qtdMateria DECIMAL(10) NOT NULL,
                PRIMARY KEY (codProduto, codMateria)
);


ALTER TABLE materia_produto ADD CONSTRAINT produto_materia_produto_fk
FOREIGN KEY (codProduto)
REFERENCES produto (codProduto)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE materia_produto ADD CONSTRAINT materia_materia_produto_fk
FOREIGN KEY (codMateria)
REFERENCES materia (codMateria)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

 

 

 

Inserindo dados e Exemplos ( OS COMENTÁRIOS NA SQL PODEM FALHAR NA EXECUÇÃO )

------ cadastrando

select *
from materia ;

insert into materia  VALUES (1 , 'Pão') ;
insert into materia  VALUES (2 , 'Presunto') ;
insert into materia  values(3, 'Alface');


INSERT INTO produto VALUES ( 1 , 'Sanduiche') ;
INSERT INTO produto VALUES ( 2 , 'Natureba') ;




SELECT * FROM MATERIA_PRODUTO;

INSERT INTO materia_produto values (1,1,1) ; 

INSERT INTO materia_produto values (1,2,3) ;  


INSERT INTO materia_produto values (2,3,2) ;  


SELECT * FROM materia_produto ; 


---- buscando por SANDUICHE , itens Pão e Presunto

	SELECT
		materia_produto.codProduto,
		produto.nomeProduto,
		COUNT(materia_produto.codProduto) maisApareceu
	FROM 
		materia_produto 
	INNER JOIN materia on materia.codMateria = materia_produto.codMateria 
	inner JOIN produto on produto.codProduto = materia_produto.codProduto
	WHERE materia_produto.codMateria in (1,2) 
	
	GROUP BY codProduto
	order by maisApareceu DESC
	limit 0,1
	




---- buscando por NATUREBA , itens Pão e Alface

	SELECT
		materia_produto.codProduto,
		produto.nomeProduto,
		COUNT(materia_produto.codProduto) maisApareceu
	FROM 
		materia_produto 
	INNER JOIN materia on materia.codMateria = materia_produto.codMateria 
	inner JOIN produto on produto.codProduto = materia_produto.codProduto
	WHERE materia_produto.codMateria in (1,3) 
	
	GROUP BY codProduto
	order by maisApareceu DESC
	limit 0,1