+ Reply to Thread
Results 1 to 2 of 2

Thread: SQL self join

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    6

    SQL self join

    I have a single table that stores week entries.
    Code:
    Id        Value     WeekId
    1         1.0000    1
    2         2.0000    1
    There can be up to three entries with the same week, So I figured using a self join would solve this
    Code:
    SELECT w1.Value, w2.Value, w3.Value 
    FROM[List].[dbo].[testWeekEntries] as w1 
    LEFT OUTER JOIN[List].[dbo].[testWeekEntries] as w2 ON w1.WeekId = w2.weekId 
    LEFT OUTER JOIN[List].[dbo].[testWeekEntries] as w3 ON w2.WeekId = w3.WeekId 
    WHERE w1.Id < w2.Id AND w2.Id < w3.Id
    The problem: It works fine with the maximum number of entries however it doesn't pull back a row with one or two entries.

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    41
    The problem is at your Where statement because your WHERE clause explicitly filters them out when the joined tables return NULL values.
    Here is the both solution for SQL-SERVER 2000 and 2008
    SQL 2000 Statement
    Code:
    SELECT  *
    FROM    (
              SELECT  (SELECT  COUNT(*) 
                       FROM    testWeekEntries 
                       WHERE   Id <= we.Id 
                               AND WeekId = we.WeekId) as rn
                      , Value
                      , WeekId
              FROM    testWeekEntries we
            ) q
    PIVOT   (MAX(Value) FOR rn IN ([1],[2],[3]) ) AS PVT
    SQL 2008 Statement
    Code:
    ;WITH q AS (
    SELECT  rn = ROW_NUMBER() OVER (PARTITION BY WeekId ORDER BY Id)
            , Id
            , Value
            , WeekId
    FROM    [testWeekEntries] as w1 
    )
    SELECT  Value
            , (SELECT Value FROM q q1 WHERE q1.rn = q.rn + 1 AND q1.WeekId = q.WeekId)
            , (SELECT Value FROM q q2 WHERE q2.rn = q.rn + 2 AND q2.WeekId = q.WeekId)
    FROM    q
    WHERE   q.rn = 1

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts