Get info from multiple tables. Join problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get info from multiple tables. Join problem?

Author

Topic

webressurs
Starting Member

5 Posts

Posted - 2007-06-08 : 06:03:42

The Stored Procedure below returns article attributes for my articles
(tblArticleAttribute). This works fine IF NOT tblArticleAttribute.Content is
empty, then it returns all templatedefinitions with the Content. But, if the
Content is empty, it also has to return all template definitions, but with
the content fields empty (now it returns nothing). This is because I bind
this information to a dynamic form, and if (example) the title or ingress is
empty the textfields (definitions) should be shown with no text. Then the
user can fill inn text an save it.

----------------------------------
Here is the SQL:
----------------------------------

SELECT tblTemplateDefinition.[Name] as TemplateDefinitionName,
tblTemplateDefinition.HelpText as TemplateDefinitionHelpText,
tblArticleAttribute.[Content], tblArticle.Id AS ArticleId,
tblTemplateDefinition.Id AS TemplateDefinitionId
FROM tblTemplateDefinition
LEFT OUTER JOIN tblArticleAttribute ON tblTemplateDefinition.Id =
tblArticleAttribute.TemplateDefinitionId
INNER JOIN tblArticle ON tblArticleAttribute.ArticleId= tblArticle.Id
WHERE tblArticle.Id = @ArticleId

----------------------------------
Here is the tables:
----------------------------------

tblArticle:
id | Name

tblArticleAttribute
TemplateDefinitionId | ArticleId | Content

tblTemplate
Id | Name

tblTemplateDefinition
Id | TemplateId | TemplateDefinitionId |Name | HelpText

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-08 : 06:36:49

Try:

SELECT D.[Name] as TemplateDefinitionName 
    ,D.HelpText as TemplateDefinitionHelpText
    ,AA.[Content]
    ,AA.ArticleId
    ,D.[Id] AS TemplateDefinitionId
FROM tblTemplateDefinition D
    LEFT JOIN tblArticleAttribute AA
         ON D.[Id] = AA.TemplateDefinitionId
            -- The following needs to be here and not the WHERE clause to keep the LEFT JOIN.
            AND AA.ArticleId = @ArticleId

webressurs
Starting Member

5 Posts

Posted - 2007-06-08 : 07:23:01

Thank you, that worked!!!

WebRessurs
<a href="http://www.webressurs.no">www.WebRessurs.no</a>

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-08 : 10:07:25

If left outer join using WHERE and AND on joined columns makes difference
http://www.sqlteam.com/item.asp?ItemID=11122

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources