>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

>
>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:

I am using Dot Net but was thinking I would end up creating a Stored Procedure for it. I have yet to use the Entity Framework, but looking forward to it. I also use MM so trying to integrate everything into my business objects. I always have the choice to use plain SQL or a stored procedure. You made that look so easy. The Linq and Linq to SQL are very cool and much easier. Thank you for your efforts in providing these, I copied them all and going to experiment so I have a better grasp.

>

 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:).

I will look into this, I could use the help this tool might provide me.
Thanks for the advice.
Tim