Re: A query question

From: Jason Miller <millerjcr_at_hotmail.com>
Date: 4 Jan 2002 10:45:54 -0800
Message-ID: <9d5d63c7.0201041045.778c1f1_at_posting.google.com>


"Larry" <schiffer52_at_attbi.com> wrote in message news:<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.

Try the following SQL:

    select a.ssn,
           a.name,
           a.custid
    from table a,
         table b

    where a.ssn = b.ssn
    and a.name = b.name
    group by a.ssn, a.name, a.custid, b.ssn, b.name     having count(distinct b.custid) > 1 Received on Fri Jan 04 2002 - 19:45:54 CET

Original text of this message