>>I have a complex join and am over my head. I am hoping to get some advice on how to do this. Let me explain.
>>
>>I have 5 tables involved.
>>Contact - Contains people names
>>Membership - Represents a membership in an organization
>>MemberContacts - is a linking table for the above
>>Membership can have many Contacts but Contacts can have only one Membership. Problem is the Contact does not have to be in Membership
>
>
>>Lot - represents all the lots in a given subdivision
>>LotOwned - is linking table to link the lots to membership
>>A lot can have only one membership, but Membership can have many lots.
>>

>>    Lot                  LotOwned                           Membership                             MemberContacts                              Contact
>>Lot.Lot_Id --> LotOwned.Lot_Id
>>                         LotOwned.Member_Id --> Membership.Member_Id
>>                                                                        Membership.Member_Id -->MemberContacts.Member_Id
>>                                                                                                                           MemberContacts.Contact_Id --> Contact.Contact_Id

>>What I want is to have a list of Contact Names for each lot. so Lot is the parent and I need Contact as a child table.
>>
>>A single record in Lot should get me a single record in LotOwned which should get me only a single record in Membership but should get me one or more records in MemberContacts with each a corresponding record in Contact.
>>Thanks for any help or advice.
>>Tim
>
>Tim,
>
>Try a variation of
>
>

>select * from (select Lot.Lot_ID from Lot INNER JOIN LotOwned on Lot.Lot_ID = LotOwned.Lot_ID) Lots 
>INNER JOIN Member_Contacts.Member_ID ON Lots.Member_ID = MemberContacts.Member_ID 
>INNER JOIN Contact ON MemberContacts.Contact_ID = Contact.Contact_ID

Naomi, After putting lots of mental effort into this last night I decided I have a data design problem. Actually when you model data after a real situation and you discover a problem it also uncovers flaws in the real situation which is the case here also. No matter how I work it I can't get around the flaw in the organization rules. I am working on a new idea that will allow more flexibility, but going to test your SQL statement before I change the data just to educate me. Thanks.
Tim