join based on criteria - SQL Server Forums

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)

join based on criteria

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-12-08 : 04:06:34

Hi

I currently have this query...

SELECT dbo.LocalizableText.InvariantText, dbo.Article.ArticleId, dbo.ImageDescription.Value, dbo.AttributeType.Code
FROM dbo.ImageDescription INNER JOIN
dbo.Article ON dbo.ImageDescription.ArticleId = dbo.Article.ArticleId INNER JOIN
dbo.Attribute ON dbo.ImageDescription.AttributeId = dbo.Attribute.AttributeId INNER JOIN
dbo.LocalizableText ON dbo.Attribute.Name = dbo.LocalizableText.LocalizableTextId INNER JOIN
dbo.AttributeType ON dbo.Attribute.AttributeTypeId = dbo.AttributeType.AttributeTypeId
WHERE (dbo.Article.ArticleId = 226)

The "dbo.AttributeType.Code" can have the value "DropDown", if it does I need to join the "dbo.ImageDescription.Value" on
" dbo.LocalizableText.LocalizableTextId" with "dbo.ImageDescription.Value" , otherwise I just want to display the value "dbo.ImageDescription.Value"

How do I make that join?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-08 : 06:03:20

I don't think you've given us enough information about the tables in question. Can you provide some table structure and required output?

I think you meant something like:


SELECT
	dbo.LocalizableText.InvariantText
	, dbo.Article.ArticleId
	, dbo.ImageDescription.Value
	, dbo.AttributeType.Code

	, CASE dbo.AttributeType.Code
		WHEN 'DropDown' THEN dbo.ImageDescription.Value
		ELSE 'dbo.ImageDescription.Value'
	END

FROM
	dbo.ImageDescription
	INNER JOIN dbo.Article ON dbo.ImageDescription.ArticleId = dbo.Article.ArticleId
	INNER JOIN dbo.Attribute ON dbo.ImageDescription.AttributeId = dbo.Attribute.AttributeId
	INNER JOIN dbo.LocalizableText ON dbo.Attribute.Name = dbo.LocalizableText.LocalizableTextId
	INNER JOIN dbo.AttributeType ON dbo.Attribute.AttributeTypeId = dbo.AttributeType.AttributeTypeId

	LEFT JOIN dbo.ImageDescription ON dbo.LocalizableText.LocalizableTextId = dbo.ImageDescription.Value
WHERE
	(dbo.Article.ArticleId = 226)

But I've got no way of knowing if the join is correct. (and I'm not really sure what you wanted to do when the velue of dbo.AttributeType.Code is not 'DropDown'

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-12-09 : 02:35:10

Hi

Thanks for your effort in trying to help me, but I decided to choose another way to retrieve what I wanted, so issue resolved. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources