>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,
With a plain SQL:

select lot.*,co.*,ms.*
from Lot
left join LotOwned lo on lo.lotID = lot.lotID
left join MemberContacts mc on mc.MembershipID = lo.MembershipID
left join Contact co on mc.contactID = co.contactID
left join MemberShip ms on ms.MembershipID = lo.membershipID

( myMS = l.Membership when you define it as 0..1)

If I don't remember wrong you are using .Net. If so, sometimes building complex SQL is easier with LINQ and you can get the generated SQL from LINQ. With Entity framework it is even easier (EF would remove the intermediate MemberContact and LotOwned and provide a smoother Lot.Membership.Contacts path). For example it looks like this in EF based Linq query:

 var lotQuery = from l in t.Lot.Include("Membership.contact")
      let myMS = l.Membership.FirstOrDefault()
      select new { l.lotName, myMS.membershipName, myMS.contact } ;

Or with Linq to SQL:

var query = from l in Lots
  let mc = l.LotOwned.Single().Membership 
  select new {l.LotName, MCName = mc.MemberContacts.Select(mcc => mcc.Contact.ContactName)};

Generated SQLs are rather complex then the plain one at top. Anyway, I use it for more complex ones when I find it hard to create an SQL but can do the same in object query. If you think about it download LinqPAD utility from www.linqPad.net. It is a cool utility to write Linq or SQL ( and 'steal' SQL if you wouldn't do anything else or use in place of SSMS for simple jobs ...) and for free if you don't choose to buy autocomplete feature (I did buy w/o a blink in eye - cheap and worth it IMHO:).