Archive for the ‘Databases’ Category

Howto: SQL / LINQ JOIN on TOP 1 row

Friday, February 5th, 2010

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

Posted in Databases, Microsoft .Net | No Comments »

Common Table Expression For Sorting Hierarchical Records By Depth

Thursday, February 26th, 2009

I tried to find this on the net, and was unable to find a satisfying and simple solution. How do you retrieve hierarchical records (say, from a self-referencing table) sorted by their depth in the hierarchy? Microsoft SQL Server 2005/2008 has a new SQL construct,  Here’s one example: a Category table with an ID (primary key) and ParentID (pointing to the hierarchical parent).

WITH CategoryCTE(ID, ParentID, Depth)
AS
(
  SELECT ID, ParentID, 0
  FROM Category
  WHERE ParentID IS NULL – root records

  UNION ALL 

  SELECT cRecursive.ID, cRecursive.ParentID, cCte.Depth+1
  FROM Category AS cRecursive JOIN CategoryCTE AS cCte
      ON cRecursive.ParentID = cCte.ID
)
SELECT *
FROM CategoryCTE
ORDER BY Depth

Posted in Databases | No Comments »

Entries (RSS) and Comments (RSS).