select (Resolvido) Consulta impossível - modelagem errada ou não sei montar a query?

[albert_assis]

Olá,

Tenho uma base simples para cadastro de propriedades de equipamentos (exemplo na imagem em anexo).

Como eu poderia selecionar, por exemplo, todos os equipamentos com Potencia>7.5 e Tensão =440?

Isso me parece simples, mas ainda não consegui fazer. Suponho que a modelagem esteja correta, logo confirmo a minha inexperiência com banco de dados!

No momento tenho a query abaixo, mas deve ter erros de lógica de "and" e "or".

SELECT e.nome
FROM equipamentos e
JOIN prop_equip pe
JOIN prop p 
ON e.id = pe.equip_id and pe.prop_id = p.id
WHERE
p.id = 1 and pn.valor > 7.5
  or p.id = 2 and pn.valor = 220;
	

Muito obrigado!!!

 

Editado Março 9, 2018 por albert_assis

[Denis Courcy]

Tente assim:

 SELECT e.id,e.nome,pe.valor
FROM equipamentos e
INNER JOIN prop_equip pe ON e.id = pe.equip_id
INNER JOIN propriedades p ON pe.prop_id = p.id
WHERE (e.id = 1 AND pe.valor > 7.5)
  OR (e.id = 2 AND pe.valor = 220);

 

[albert_assis]

@Denis Courcy,

Fiz o teste conforme a sua sugestão mas ainda não consegui o resultado esperado. Desta forma retornam equipamentos que não atendem às restrições.

Consegui obter os resultados utilizando a sub-query abaixo, mas não sei o quanto isso vai influenciar na performance, pois em cada loop de sub-query toda a tabela será escaneada. Vou testar em um BD de simulação.

SELECT distinct e.id, e.nome
FROM prop_num_equip pn
JOIN equip e
ON e.id = pn.EQUIP_id
where pn.prop_id = 2 and pn.valor >220
   and pn.EQUIP_id in
(SELECT pn.EQUIP_id
FROM prop_num_equip pn
where pn.prop_id = 1 and pn.valor = 7.5)

Muito obrigado!!

 

Editado Março 12, 2018 por albert_assis

[albert_assis]

Saudações!

Montei o banco de simulação com 20.000 equipamentos, 50 propriedades e 200.000 propriedades de equipamentos (10 propriedades para cada equipamento.

O tempo de resposta do script com sub-query e duas restrições de propriedades foi de 32 segundos!! (Win7, i5, 3,2Ghz, 4GB Ram).  Com apenas uma restrição demora apenas 0.2 segundos.

Sendo assim, acredito que a solução com sub-query não é uma boa opção por causa da performance.

Alguém tem alguma ideia melhor?

Muito obrigado!!

 

[albert_assis]

A consulta abaixo encontrou os equipamentos que atendem todas as restrições.

SELECT e.nome
FROM prop_num_equip pn
JOIN equip e
JOIN prop p
ON e.id = pn.EQUIP_id and pn.PROP_id = p.id

WHERE
(pn.valor = 135 and pn.PROP_id = 5)
or
(pn.valor = 947 and pn.PROP_id = 28)
or
(pn.valor = 567 and pn.PROP_id = 30)

GROUP BY pn.EQUIP_id
HAVING COUNT(pn.EQUIP_id) = 3;
	

O JOIN é apenas para resgatar o nome do equipamento de sua respectiva tabela.

As restrições em "WHERE" trazem todos os equipamentos que atendam uma restrição.

As cláusulas "GROUP BY" e "HAVING COUNT ="  selecionam apenas os equipamentos que atenderam todas as restrições, pois somente estes casos aparecem mais de uma vez no resultado.

O número 3 na expressão "HAVING COUNT(pn.EQUIP_id) =3;" tem que ser igual ao número de restrições.

Esta discussão sobre encontrar registros duplicados foi a referencia para chegar nesta solução.

Muito obrigado a todos!!

"O conhecimento dividido é multiplicado"

Editado Março 15, 2018 por albert_assis