Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
webressurs
Starting Member
5 Posts |
 Posted - 2007-06-28 : 05:04:29
|
| I 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.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 AttributeValueFROM 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.ArticleIdWHERE (dbo.tblArticle.ParentId = 3) AND (dbo.tblTemplate.Id = 1)I have made a View that returns 2 articles like this: ID Publish start ArticleName TemplateID TemplateName AttributeName AttributeValue4 22.05.2007 Article 1 1 Article Title Welcome4 22.05.2007 Article 1 1 Article Ingress Hello4 22.05.2007 Article 1 1 Article Body Cool4 22.05.2007 Article 1 1 Article Info Yeah..5 26.06.2007 Article 2 1 Article Title ByeBye5 26.06.2007 Article 2 1 Article Ingress Ughhh5 26.06.2007 Article 2 1 Article Body Nice5 26.06.2007 Article 2 1 Article Info ArrrgAs 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!!!www.WebRessurs.no |
|
|
|
|
|
|
|
Stored procedure with Dynamic columns?