>>>>>>select membership.name;
>>>>>> from membership;
>>>>>> where membership.name = membership.name;
>>>>>> and membership.city = membership.city;
>>>>>> and membership.ssn <> membership.ssn;
>>>>>
>>>>>Yeah- I was hoping to do something like this, but this would result in a circular relation ship.
>>>>>
>>>>>I believe that the ultimate solution will involve several generations of sub tables and complex, multi field indexes.
>>>>>
>>>>>Thanks again for your input.
>>>>
>>>>Erik,
>>>>
>>>>It is perfectly valid to join a table to itself (though I may have the syntax wrong).
>>>>
>>>>I hope you find what you are looking for.
>>>
>>>Yeah, I join tables to themselves regularly (this is called a unary relationship), (and I think I included the correct syntax in my response), but you can't do it twice, on two different fields, in the same query. The multiple joins are what creates the circular relatonship, just like when you join two different tables on two different fields.
>>>Thanks again for your suggestion.
>>
>>Erik,
>>
>>I just tried the following example. It works.
>>
>>SELECT Test.name, Test.city;
>> FROM test, test Test_a;
>> WHERE Test_a.id <> Test.id;
>> AND (Test.name = test_a.name;
>> AND Test.city = test_a.city)
>>
>>TEST.DBF has the follow data
>>
>>ID NAME CITY
>>1 JIM VANCOUVER
>>2 JIM VANCOUVER
>>3 JIM SEATTLE
>>4 BILL VANCOUVER
>>5 JANE TORONTO
>>
>>The result is two records
>>
>>JIM VANCOUVER
>>JIM VANCOUVER
>>
>>Since both of Jim's records match the other.
>>
>>You can join a table using multiple fields and it is valid, so I believe that you are incorrect in saying that this is a circular relationship.
>>
>>Even if this isn't the solution to your problem, I still think this is a valid join and serves the purpose of finding duplicate name/city with a different unique id.
>>
>>I would like to hear from Dorris, Chuck, Mark and Barbara (or others) on this SQL question.
>
>I believe that you are right... the SQL you have shown should (and apparently does) work, but in my case (and I don't think I understand why) relating the table to itself usinf moer than one field has returned odd and incorrect results. It could possible be the order of the joins? I'm not sure. I would also like to hear from the other as to how this works.

Erik,

An interesting point... I let the query builder make the SQL for me. I think that I would not have made an alias for the second reference to the table if I would have coded the SQL by hand. This might have exhibited the the behaviour that you were referring to. Interesting.