Quering multiple tables - 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)

Quering multiple tables

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 03:03:32

Lets say I have a table with categories, a table with articles and a table to link the two (ArticleCategories).

So articlecategories will hold articleid's and categoryid's

I would like to return an article:
SELECT
[ArticleID],
[ArticleTitle],
[ArticleAuthor],
[ArticleText],
etc.
And something like [ArticleCategories] which would give me a comma seperated list of categories (3, 6, 7, 10).

How would I do that. Do I need a function for this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 03:07:42

see http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 03:19:34

Perhaps I didn't explain right.
I don't want a column in the articles table with the category id's.
I just want a stored procedure which output's a field.

So articles might look like
ArticleID
Articletitle
ArticleSummary
ArticleText

And when I do the select query it outputs
ArticleID
ArticleTitle
ArticleSummary
ArticleText
CategoryIDs

And the SP gets the category id's from the articlecategories table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 03:29:40

create a UDF to concatenate the Category ID. (UDF from the article link)


CREATE FUNCTION dbo.ConcatCategory(@ArticleID VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SET @Output = ''

	SELECT @Output =	CASE @Output 
				WHEN '' THEN CategoryID
				ELSE @Output + ', ' + CategoryID 
				END
	FROM  articlecategories
	WHERE ArticleID = @ArticleID
	ORDER BY CategoryID

	RETURN @Output
END

Use the UDF in your select statement


SELECT ArticleID,
       ArticleTitle,
       ArticleSummary,
       ArticleText,
       CategoryIDs = dbo.ConcatCategory(ArticleID)
FROM   articles

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 03:47:22

Allright I got it.

Thanks a very very very lot and much

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 03:52:45

One thing though

When I pass @ArticleID 11 I get an error saying:
Conversion failed when converting the varchar value '11, ' to data type int.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 03:57:04

your Article ID is integer or char / varchar ?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 03:58:51

It's an integer,
And when I enter @articleid = 13, it seems to return the category's as 13 as well

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 04:10:18

For SQL Server 2005, you don't need a UDF for this.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Peter Larsson
Helsingborg, Sweden

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 04:12:40

As long as there is 1 category it goes ok because it returns something like (1), if there is more then 1 category, it gives me the error because it returns something like (1, 2).
It seems it expects the output to be an integer.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 04:14:24

quote:
Originally posted by Peso

For SQL Server 2005, you don't need a UDF for this.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Peter Larsson
Helsingborg, Sweden

It needs to be working on SQL 2000 and 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 04:15:45

Ok.
Then you should have posted this in a SQL 2000 forum, or told us in the original post.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 04:16:44

quote:
Originally posted by trouble2

It's an integer,
And when I enter @articleid = 13, it seems to return the category's as 13 as well

Please post your table DDL, some sample data and expected result.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 04:17:31

quote:
Originally posted by Peso

Ok.
Then you should have posted this in a SQL 2000 forum, or told us in the original post.

Peter Larsson
Helsingborg, Sweden

You're right, I didn't realise it, but this is a 2005 forum....
Sorry, well, it needs to work on both.
But I think the UDF should work on 2000 to should'nt it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 04:21:57

quote:

Originally posted by khtan

CREATE FUNCTION dbo.ConcatCategory(@ArticleID VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SET @Output = ''

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

	RETURN @Output
END

Peter Larsson
Helsingborg, Sweden

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 04:22:06

quote:
Originally posted by khtan

quote:
Originally posted by trouble2

It's an integer,
And when I enter @articleid = 13, it seems to return the category's as 13 as well

Please post your table DDL, some sample data and expected result.

KH

OK, here goes:

articlecategories should be something like

articleid categoryid
1 1
1 2
2 1

articles should be like

articleid articletitle articletext
1 This is title 1 This is text 1
2 This is title 2 This is text 2
3 This is title 3 This is text 3

So when I call GetArticle I expect

GetArticle @articleid = 1
returns categoryids 1, 2

GetArticle @articleid = 2
returns categoryids 1

Getarticle @articleid = 3
returns categoryids ''

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-26 : 04:25:44

quote:

Originally posted by Peso

quote:

Originally posted by khtan

CREATE FUNCTION dbo.ConcatCategory(@ArticleID VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SET @Output = ''

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

	RETURN @Output
END

Peter Larsson
Helsingborg, Sweden

PETER !!!!!

You're a hero.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 04:27:27

Not really.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources