Re: A query question

crappygolucky_at_hotmail.com (crappy) wrote in message news:<ce31c410.0201041101.78e33c5b_at_posting.google.com>...
> uhh, judging from your attempt maybe i don't really understand the
> problem, but why can't you just do:
>
> select ssn, name, custid from table a, table b where a.ssn = b.ssn and
> a.name = b.name and a.custid != b.custid;
>
> ?

That would work for the sample table, but if there were three or more different custid's for a ssn/name combination, you'd start to have problems. I'm assuming that the sample data was simplified and just given as an example. Take the following hypothetical sample table and consider what the above SQL code would produce.

1234 greg 0001 good
1234 greg 0002 good
1234 greg 0003 good
7777 anne 0004 good
7777 anne 0004 bad
7777 anne 0005 good

The resulting output from your SQL would be:

1234 greg 0001
1234 greg 0001
1234 greg 0002
1234 greg 0002
1234 greg 0003
1234 greg 0003
7777 anne 0004
7777 anne 0004
7777 anne 0005
7777 anne 0005

While you could put a distinct on the select (e.g. select distinct ssn, name, custid), it wouldn't actually be showing all the records for customers that had multiple custid's. In this case, anne has two records for 0004, and a distinct would make it appear she only had one. Depending on what information the programmer really needs, distinct records might be sufficient. Received on Fri Jan 04 2002 - 23:17:28 CET