A SOLUTION FOR THAT QUERY

  • Thread starter Thread starter Frank Dulk
  • Start date Start date
F

Frank Dulk

It follows the following query below:

Select rel_30.cpf, rel_30.cliente, rel_30.enderecoentrega
from rel_30
where (((rel_30.enderecoentrega)In (Select FROM [enderecoentrega] FROM
[Rel_30] As Tmp GROUP BY [enderecoentrega]HAVING
Count(enderecoentrega)>2)));

* Of the way that is brings the following result, former:

CUSTOMER CPF ENDERECOENTREGA

Fulano de tal 961232333-87 Rua Hum 10
Fulano de tal 961232333-87 Rua Hum 10
Fulano de tal 961232333-87 Rua Hum 10
Beltrano de tal 333333333-00 Rua Dois 20
Beltrano de tal 333333333-00 Rua Dois 20
Beltrano de tal 444444444-00 Rua Dois 20

In other words, more than two addresses similar with different CPFs, BUT
also brought same addresses and same CPFs, the first 3 RECORDS don't want,
only the remaining.
I DON'T GET ANY IN WAY TO FIND A SOLUTION FOR THAT QUERY
 
I Apologize for not getting your english so well, but if I understand you,
then this should work.. Let me know:

Select R.cpf, R.cliente, R.enderecoentrega
from rel_30 R
Where (Select Count(*) From Rel_30
Where enderecoentrega = R.enderecoentrega) > 2
And Exists
(Select * From Rel_30
Where enderecoentrega = R.enderecoentrega
Group By cpf
Having Count(*) > 1)
 
Hi Frank

Maybe you need something like:

Select DISTINCT r.cpf, r.cliente, r.enderecoentrega
from rel_30 r
where EXISTS (
Select 1 FROM [Rel_30] s
WHERE r.enderecoentrega = s.enderecoentrega
AND r.cpf = s.cpf
GROUP BY s.[enderecoentrega]
HAVING Count(DISTINCT s.cliente) > 1 )

or
Select DISTINCT r.cpf, r.cliente, r.enderecoentrega
from rel_30 r
where (
SELECT Count(DISTINCT s.cliente) FROM [Rel_30] s
WHERE r.enderecoentrega = s.enderecoentrega
AND r.cpf = s.cpf ) > 1

Check out how to post DDL and example data at
http://www.aspfaq.com/etiquett­­­e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.­­­htm#inserts
regarding what is useful when posting a question.

John
 
Back
Top