Combining n-to-n query - 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)

Combining n-to-n query

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-07-08 : 06:27:50

I have a table with information like this:

Articles
articleid title text
1 BlaBla BlaBlabla
2 sometitle sometext

And each record can have a maximum of 3 keywords assigned to it using a n-to-n relatioship.

So we have
ArticlesKeywords
articleid keywordid
1 1
1 2
2 1
2 4

And

Keywords
keywordid keyword
1 kw1
2 kw2
3 kw3
4 kw4

So the idea is to return the results as:

Articleid title text keywords
1 BlaBla Blablabla kw1, kw2
2 sometitle sometext kw1, kw4

What would be the best way to do this in a SP?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 06:34:10

concatenate records without UDF

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 06:42:07

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-07-08 : 06:42:45

Yes, well I actually used a function for returning the ID's from the between-table like so:

ALTER FUNCTION [dbo].[ConcatKeywords](@ARTICLEID VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN CAST(KeywordID AS VARCHAR)
ELSE @Output + ', ' + CAST(KeywordID AS VARCHAR)
END
FROM ArticlesKeywords
WHERE ArticleID = @ARTICLEID
ORDER BY KeywordID

RETURN @Output
END

So it would return:
Articleid title text keywords
1 BlaBla Blablabla 1, 2
2 sometitle sometext 1, 4

But I don't know how to take the next step...

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-07-08 : 06:59:03

So would I do something like:

SELECT @Output = CASE @Output
WHEN '' THEN CAST(KeywordName AS VARCHAR)
ELSE @Output + ', ' + CAST(KeywordName AS VARCHAR)
END
FROM ArticlesKeywords left join Keywords
ON .....

WHERE ArticlesKeywords.ArticleID = @ARTICLEID

ORDER BY ArticlesKeywords.KeywordID

Or can it be done better (probably...)

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-07-08 : 07:13:44

Yes, well it's working with the left join in the function, but I can imagine there might be a better way.
Or is there?

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources