Daisy chained SQL Records

Question

Suppose I have table called Messages with the following fields:

MessageID,
MessageTitle,
MessageBody,
MessageDate,
ReplyTo

The ReplyTo field is of int type and can be null (when the message is not a reply to another message) or the value of some other message's MessageID. So, a message can, optionally, be set as a reply to an pre-existing message.

Is it possible to have a query that returns a list of records, starting from a particular message and followed by the record to which it is replying. This second record would, in turn, be followed by the message it is a reply to....and so on. I guess there is some kind if recursion involved.

Apologies for not using the correct terminology for what I am trying to achieve. I don't know what it is really called and therefore I don't know what to look for in Google.

Yes, this is quite straightforward with the Common Table Expression syntax. The MSDN for CTE's shows how to build a hierarchy for an org chart, which is very similar to what you are looking for. THis search string returns loads of links: "tsql CTE hierarchy" — Pieter Geerkens, May 01 '13 at 06:20
take a look here: http://stackoverflow.com/questions/16282702/sql-recursive-query-only-return-the-last-row/16282874#16282874 — bummi, May 01 '13 at 06:53

score 2 · Accepted Answer · answered May 01 '13 at 06:56

2

Hope this helps as approach:

CREATE TABLE [dbo].[messages](
    [id] [int] NOT NULL,
    [replyto] [int] NOT NULL,
    [title] [nvarchar](max) NULL,
    [body] [nvarchar](max) NULL,
    [created_at] [datetime] NULL
) ON [PRIMARY]

Then select data like this:

WITH Children (id, replyto, title, body, created_at) AS (
  SELECT id, replyto, title, body, created_at
  FROM [messages]
  WHERE replyto = -1 -- all msg from root
  UNION ALL
  SELECT b.id, b.replyto, b.title, b.body, b.created_at 
  FROM Children a, [messages] b
  WHERE a.id = b.replyto
)
SELECT id, replyto, title, body, created_at FROM Children

In my sample replyto is not nullable so I defined -1 as root messages. If you want a specific message + their childs then you modify the first SELECT statement.

answered May 01 '13 at 06:56

YvesR

5,922
6
43
70

I din't quite understand the part where you said "If you want a specific message + their childs then you modify the first SELECT statement." How should it be modified? – Osprey May 01 '13 at 07:53
OK. Made it work by replacing "FROM Children a, [messages] b WHERE a.id = b.replyto" with "from [Messages] as b inner join Children as r on b.id= r.replyto" Thanks! – Osprey May 01 '13 at 08:09
@Osprey I ment replace `WHERE replyto = -1` with e.g. `WHERE [messages].[id] = 12345` to choose a specific message. Then you get this message + their childs. – YvesR May 01 '13 at 10:18

Daisy chained SQL Records

1 Answers1