am making a CMS solution and need help to get out an article list. The reason why this is a bit tricky is because articles does not have the same fields stored in one static article-table. In tblArticle all standard information about each article is stored. In tblArticleAttribute all different kind on information for an article is stored, defines by the templateDefinition table.

Expample: Template 1 has Title, ingress, body). In tblArticleAttribute all content is stored, for example Title Text, Ingress Text and Body text for ArticleId1 and TemplateDefinitionId 1.

I have made a View that returns 2 articles like this:

SELECT     dbo.tblArticle.Id, dbo.tblArticle.PublishStart, dbo.tblArticle.Name AS ArticleName, dbo.tblTemplate.Id AS TemplateId, 
                      dbo.tblTemplate.Name AS TemplateName, dbo.tblTemplateDefinition.Name AS AttributeName, dbo.tblArticleAttribute.[Content] AS AttributeValue
FROM         dbo.tblArticle INNER JOIN
                      dbo.tblTemplate ON dbo.tblArticle.TemplateId = dbo.tblTemplate.Id INNER JOIN
                      dbo.tblTemplateDefinition ON dbo.tblTemplate.Id = dbo.tblTemplateDefinition.TemplateId INNER JOIN
                      dbo.tblArticleAttribute ON dbo.tblTemplateDefinition.Id = dbo.tblArticleAttribute.TemplateDefinitionId AND 
                      dbo.tblArticle.Id = dbo.tblArticleAttribute.ArticleId
WHERE     (dbo.tblArticle.ParentId = 3) AND (dbo.tblTemplate.Id = 1)

The View return 2 articles:

ID | Publish start | ArticleName | TemplateID | TemplateName | AttributeName| AttributeValue
4	22.05.2007	Article1	1		Article		Title		Welcome
4	22.05.2007	Article1	1		Article		Ingress		Hello
4	22.05.2007	Article1	1		Article		Body		Cool
4	22.05.2007	Article1	1		Article		Info		Yeah..
5	26.06.2007	Article2	1		Article		Title		ByeBye
5	26.06.2007	Article2	1		Article		Ingress		Ughhh
5	26.06.2007	Article2	1		Article		Body		Nice
5	26.06.2007	Article2	1		Article		Info		Arrrg

As you see it return 4 rows per article. This is because the Article template has 4 templateDefinitions. Another template can have more or less templatedefinitions. Is it possible to make a query based on this View that return 1 record for each article? Then each AttributeName shall be a column name, and the attributeValue shall be the value for this Column.

This is really hard to make, ugh!!!

10 Years
Discussion Span
Last Post by sibir1us

There are couple things:

1. Read some article on database normalization and revisit your database model

2. don't use views if you can. Views are in general a bad idea in SQL Server.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.