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
Timothy Bryan