SQL self join multiple times

Question

I have a single database table that stores week entries.

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

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 worls fine with the maximum number of entries however it doesn't pull back a row with one or two entries.

Is there a different type of join I can use to pull back a row with only one or two entries or a different way of approaching this?

Lieven Keersmaekers · Accepted Answer · 2012-01-17T12:50:28.913

2

These entries are not returning because your WHERE clause explicitly filters them out when the joined tables return NULL values.

This solution adds a sequential rownumber to each record, restarting to 1 for each week. This allows you to use this sequential number in a PIVOT statement

SQL 2000 Statement

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

;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

edited Jan 17 '12 at 12:50

answered Jan 15 '12 at 12:29

Lieven Keersmaekers

57,207
13
112
146

I tried this however it now brings back multiple rows where it should only bring back one row per week Id – user900566 Jan 15 '12 at 12:35
@user900566 - you are right, my mistake. What DBMS are you using? – Lieven Keersmaekers Jan 15 '12 at 12:39
@user900566 - I have adjusted the answer to adres the bug. – Lieven Keersmaekers Jan 15 '12 at 12:58
@user900566 - to be honest, I have this nagging feeling it can be simplified but I can't seem to get it. – Lieven Keersmaekers Jan 15 '12 at 13:19
just realised that the productino server is ms sql 2000 and doesnt have row_number() functionality – user900566 Jan 17 '12 at 11:19
@user900566 - I have added a SQL Server 2000 solution – Lieven Keersmaekers Jan 17 '12 at 12:50

score 1 · Answer 2 · answered Jan 15 '12 at 12:31

1

You will need to add in your where clause the possibility that w2.Id is null or w3.id is null

So something like

WHERE 
  (w2.Id is null and w3.id is null) or 
  (w3.id is null and w1.id < w2.id) or 
  (w1.id < w2.id and w2.id < w3.id)

answered Jan 15 '12 at 12:31

craigmj

4,827
2
18
22

score 1 · Answer 3 · answered Jan 15 '12 at 13:17

1

You can also use PIVOT

;WITH CTE AS
(
SELECT Value,
       WeekId,
       ROW_NUMBER() OVER (PARTITION BY WeekId ORDER BY Id) AS RN
FROM   [List].[dbo].[testWeekEntries]       
)
SELECT *
FROM CTE 
PIVOT (MAX(Value) FOR RN IN ([1],[2],[3]) ) AS PVT

answered Jan 15 '12 at 13:17

Martin Smith

438,706
87
741
845

+1 Thx Martin, PIVOT is still not my cup of tea but I have to ask: isn't there an easier solution to this without resorting to a CTE and adding a ROW_NUMBER. – Lieven Keersmaekers Jan 15 '12 at 13:23
@Lieven - Not that I can think of! – Martin Smith Jan 15 '12 at 13:33
1

You have no idea how comforting that is for me . – Lieven Keersmaekers Jan 15 '12 at 13:35

SQL self join multiple times

3 Answers3

Linked