(Resolvido) Consulta

[laine]

Olá pessoal estou precisando de ajuda..

eu uso banco em fireibird e tenho uma tabela de fornecedores onde eu informo os dados de nome, endereço e telefone e tenho uma tabela fornecedores2 onde eu informo os produtos que esse fornecedor vende, e cadastro codigo do produto e descricao, ou seja para cada fornecedor eu posso ter varios produtos , e agora começa o problema eu preciso fazer um filtro para localizar somente os fornecedores que tenham determinados produtos eu preciso informar quais produtos eu quero e o filtro me mostrar quais fornecedores vendem tais produtos..

eu estou usando o seguinte select:

select distinct a.* from fornecedor a left outer join fornecedor2 b on a.id=b.id_fornecedor

where b.codigo in (11,7) , mas esse select me retorna todos os fornecedores que tem o produto 11 e todos os fornecedores que vendem o produto 7 e eu preciso que me retorne apenas os fornecedores que vendam os 2 produtos tanto o produto 11 e o produto 7

alguém pode me ajudar...

[Micheus]

(...) eu estou usando o seguinte select:

select distinct a.* from fornecedor a left outer join fornecedor2 b on a.id=b.id_fornecedor

where b.codigo in (11,7) , mas esse select me retorna todos os fornecedores que tem o produto 11 e todos os fornecedores que vendem o produto 7 e eu preciso que me retorne apenas os fornecedores que vendam os 2 produtos tanto o produto 11 e o produto 7

laine, como sua consulta procura pelo produto específico para chegar nos fornecedores, eu penso que sua consulta ficaria mais clara se o select principal ocorrer sobre a tabela dos produtos fornecedor2:

select distinct B.id_fornecedor, A.*
from fornecedor2 B, fornecedor A
where B.id_fornecedor = A.id and B.codigo in (7, 11)

Quanto a filtrar os dois códigos ao mesmo tempo, vou fazer uns testes antes de dar uma sugestão. :blush:

Abraços

Editado Março 23, 2009 por Micheus

[Micheus]

Tentando explicar o que eu havia citado antes...

select distinct B.id_fornecedor, A.*
from fornecedor2 B
left join fornecedor A on (B.id_fornecedor = A.id)
where B.codigo in (7, 11)

Será procurado na tabela fornecedor2 os registros que contém codigo = 7 ou 11. Para cada um que for encontrado, será buscado o registro em fornecedor. Uma sugestão seria a seguinte:

select A.*
from fornecedor A
where exists(select first 1 B.codigo
             from fornecedor2 B 
             where B.id_fornecedor = A.id 
               and B.codigo = 7)
  and exists(select first 1 B.codigo
             from fornecedor2 B 
             where B.id_fornecedor = A.id 
               and B.codigo = 11)

Será procurado na tabela fornecedor os registros onde existam resultados para as sub-consultas realizadas na tabela fornecedor2. Apenas os registros de forncedor em que o campo id for encontrado nas sub-consultas em fornecedor2 juntamente com seu campo codigo igual ao valor passado serão retornados. (as duas sub-consulta devem retornar verdadeiro). Quantos códigos você tiver que procurar, mais sub-selects tem que ser incluídos. O uso do first, ao invés do distinct, otimiza a consulta no banco. Se você usa o IBExpert, poderá observar isto. No exemplo que usei, estes foram os planos adaptados e enviados ao banco: First: PLAN (EP INDEX (INTEG_39)) PLAN (EP INDEX (INTEG_39)) PLAN (P NATURAL) Distinct: PLAN SORT ((EP INDEX (INTEG_39))) PLAN SORT ((EP INDEX (INTEG_39))) PLAN (P NATURAL) Outra opção que pode lhe servir, já que trará dados distintos, seria usar a cláusula group by com having count, mas precisamos declarar na cláusula select as colunas a serem usadas:

select A.id, A.nome
from fornecedor2 B
left join fornecedor A on (B.id_fornecedor = A.id)
where B.codigo in (7, 11)
group by A.id, A.nome
having count(A.id) = 2

onde, este 2 corresponde a quantidade de elementos na lista - só assim, você garante que estará trazendo a informação desejada.

Funcionou com meu exemplo. Verifique se vale para você.

Espero que uma destas sugestões lhe ajude.

Abraços

[laine]

Tentando explicar o que eu havia citado antes...

select distinct B.id_fornecedor, A.*
from fornecedor2 B
left join fornecedor A on (B.id_fornecedor = A.id)
where B.codigo in (7, 11)

Será procurado na tabela fornecedor2 os registros que contém codigo = 7 ou 11. Para cada um que for encontrado, será buscado o registro em fornecedor. Uma sugestão seria a seguinte:

select A.*
from fornecedor A
where exists(select first 1 B.codigo
             from fornecedor2 B 
             where B.id_fornecedor = A.id 
               and B.codigo = 7)
  and exists(select first 1 B.codigo
             from fornecedor2 B 
             where B.id_fornecedor = A.id 
               and B.codigo = 11)

Será procurado na tabela fornecedor os registros onde existam resultados para as sub-consultas realizadas na tabela fornecedor2. Apenas os registros de forncedor em que o campo id for encontrado nas sub-consultas em fornecedor2 juntamente com seu campo codigo igual ao valor passado serão retornados. (as duas sub-consulta devem retornar verdadeiro). Quantos códigos você tiver que procurar, mais sub-selects tem que ser incluídos. O uso do first, ao invés do distinct, otimiza a consulta no banco. Se você usa o IBExpert, poderá observar isto. No exemplo que usei, estes foram os planos adaptados e enviados ao banco: First: PLAN (EP INDEX (INTEG_39)) PLAN (EP INDEX (INTEG_39)) PLAN (P NATURAL) Distinct: PLAN SORT ((EP INDEX (INTEG_39))) PLAN SORT ((EP INDEX (INTEG_39))) PLAN (P NATURAL) Outra opção que pode lhe servir, já que trará dados distintos, seria usar a cláusula group by com having count, mas precisamos declarar na cláusula select as colunas a serem usadas:

select A.id, A.nome
from fornecedor2 B
left join fornecedor A on (B.id_fornecedor = A.id)
where B.codigo in (7, 11)
group by A.id, A.nome
having count(A.id) = 2

onde, este 2 corresponde a quantidade de elementos na lista - só assim, você garante que estará trazendo a informação desejada.

Funcionou com meu exemplo. Verifique se vale para você.

Espero que uma destas sugestões lhe ajude.

Abraços

Obrigada Micheus, resolveu o meu problema muito obrigada mesmo

Abraços

[Micheus]

laine, a primeira opção com os exits, com certeza funciona. Mas a segunda opção que postei (aquela que usa o haging count), refletindo um pouco melhor, acho que ela está errada. Peço a gentileza de que você teste com sua base e confirme se está funcionando como deveria.

Infelizmente minha base para o teste não era muito grande e eu acho que o resultado que tive foi apenas coincidência. Vou tentar montar uma base maior e avaliar melhor esta sugestão.

Abraços

[Micheus]

Mas a segunda opção que postei (aquela que usa o haging count), refletindo um pouco melhor, acho que ela está errada.

Ela funciona na condição em que apenas tenhamos que levar em conta o fornecedor. Se houvesse a possibilidade de, na tabela fornecedor2, haver dois registros para o mesmo produto e produtor, então o resultado não seria correto.

select A.id, A.nome
from fornecedor2 B
left join fornecedor A on (B.id_fornecedor = A.id)
where B.codigo in (7, 11)
group by A.id, A.nome
having count(A.id) = 2

Um teste de mesa, para exemplificar...

fornecedor2:

codigo|id_fornecedor|descricao

5|2|produto cod 5

5|11|produto cod 5

5|18|produto cod 5

7|5|produto cod 7

7|8|produto cod 7

7|11|produto cod 7

8|5|produto cod 8

8|12|produto cod 8

11|5|produto cod 11

11|6|produto cod 11

11|11|produto cod 11

11|12|produto cod 11

12|1|produto cod 12

12|3|produto cod 12

dos candidatos, B.codigo in (7, 11) resulta em:

7|5|produto cod 7

7|8|produto cod 7

7|11|produto cod 7

11|5|produto cod 11

11|6|produto cod 11

11|11|produto cod 11

11|12|produto cod 11

B.id_fornecedor corresponde à A.id assim, o resulta será agrupado na sequência:

7|5|produto cod 7

11|5|produto cod 11

11|6|produto cod 11

7|8|produto cod 7

7|11|produto cod 7

11|11|produto cod 11

11|12|produto cod 11

o resultado será agrupado pelos campos id e nome do fornecedor e o agrupamento por A.id resulta em:

5|fornecedor 5

5|fornecedor 5

6|fornecedor 6

8|fornecedor 8

11|fornecedor 11

11|fornecedor 11

12|fornecedor 12

e apenas os códigos filtrados que aparecem 2 vezes serão retornados ao ser considerado count = 2.

Para fins de melhor performance nas consultas, é conveniente que você tenha um índice, na tabela fornecedor2, cujo primeiro campo seja codigo, bem como para tabela fornecedor exista um com o campo id na mesma posição.

Abraços