Retrieving Content from a Sub Content

Question

I'm using SQL Server 2k8 and have a table that needs to be able to pull dynamic content. So, I was thinking of having 2 columns which would act like a Parent and Child.

ContentId - SubContentId - ContentInfo
100         100            data here
101         100            data here
102         100            data here
103         100            data here

So, although the ContentId is incrementing, the SubContentId relates to the Parent ContentId of 100. So if I SELECT ContentId 100, I'd check the SubContentId for this number as well in order to pull back all of the details for ContentId 100. Is this approach sensible? Am I way of the mark?

Answer 1

Create, setup, test:

CREATE TABLE #tmp (
    ContentID int, SubContentID int, ContentInfo varchar(255)
)

INSERT INTO #tmp VALUES (100, 100, 'info 1')
INSERT INTO #tmp VALUES (101, 100, 'info 2')
INSERT INTO #tmp VALUES (102, 100, 'info 3')
INSERT INTO #tmp VALUES (103, 100, 'info 4')
INSERT INTO #tmp VALUES (104, 100, 'info 5')

SELECT ContentInfo, 
          (SELECT ContentInfo + ',' as "data()"  
           FROM #tmp x 
           WHERE x.SubContentID = z.ContentID FOR XML PATH('')
          ) As [SubContent]  
FROM #tmp z
WHERE ContentID = 100

Answer 2

You have a self referencing table. This isn't all bad. All you have to do is create a query like this.

SELECT * FROM content where subcontentID = contentID AND contentID = @contentID

The results will be all the content records that have the specified contentID

An ordered result set might be nice if you need to see all the data too.

SELECT * FROM content where subcontentID = contentID ORDER BY subcontentID,contentID

This will give you all the content records keeping all the subcontent records together in order.

This is not bad at all it saves from creating a duplicate table with the same schema.