Problem: you want to do a LEFT or INNER JOIN between two tables but include only one record from the other table: that is, you don’t want the join to create duplicate records. Interestingly enough, I found the solution to this through LINQ. In LINQ, you can do this without really thinking about it:
from cmp in ctx.Companies join pers in ctx.Persons on cmp.Persons.First().ID equals pers.ID from cmp in ctx.Companies join pers in ctx.Persons on cmp.Persons.First().ID equals pers.ID
Surprisingly for me, this query gets translated into working SQL, which looks something like this (note that I cleaned it up quite a bit for readability):
view plaincopy to clipboardprint
FROM Company
INNER JOIN Person ON
(
SELECT TOP (1) top1Person.ID
FROM Person AS top1Person
WHERE top1Person.CompanyID = Company.ID
) = Person.ID
FROM Company
INNER JOIN Person ON
(
SELECT TOP (1) top1Person.ID
FROM Person AS top1Person
WHERE top1Person.CompanyID = Company.ID
) = Person.ID
Once you think about it, the solution is quite simple. All you need to remember is that a JOIN can contain subselects (even subselects with their own JOINs).