Howto: SQL / LINQ JOIN on TOP 1 row
- Written by Boris Drajer
- Published in Databases
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).