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

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

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