A query question

From: Larry <schiffer52_at_attbi.com>
Date: Fri, 04 Jan 2002 04:47:12 GMT
Message-ID: <ktaZ7.3106$B85.59518_at_rwcrnsc53>

Good Day...

Interesting problem and am not sure sure how to solve it...any advise is appreciated....

Table structure:
ssn name custid status
1234 greg 0001 good
1234 greg 0002 good
7777 anne 0003 good
7777 anne 0003 bad
8888 juan 0004 good
8888 juan 0004 bad
9999 harry 0005 good
9999 harry 0006 bad

The result set should only be those rows where the ssn is the same, the name is the same and the custid IS DIFFERENT ...thus, the only rows returned would be those containing grge and harry.

The SQL I have returns all the rows..
select a.ssn,

          b.name,
        c.custid
from   table a,
         table b,
         table c

where a.ssn=b.ssn

and b.ssn=c.ssn
and a.name=b.name
and b.name =c.name
and a.custid = b.custid

group by a.ssn,b.name,c.custid
having count(a.ssn)>1 and count(b.name) >1

This SQl returns event hose rows that I do not want in the result set (juan, anne).

Any suggestions greatly appreciated.

Thnaks in advance. Received on Fri Jan 04 2002 - 05:47:12 CET