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