subquery question - 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 2000 Forums

SQL Server Development (2000)

subquery question

Author

Topic

settinUpShop
Starting Member

28 Posts

Posted - 2003-11-19 : 10:52:23

Is it possible to run sub queries in a select statement where you declare the columns you'd like to return?

In my sub query I want to return a count of the number of records in a related table that have the same ID number.

PageInfo table has one to many relationship to PageInfo_MetaKeyword table which has a many to one relationship to MetaKeyword table.

I want to show for each PageInfo record how many MetaKeywords it has, OR even better would be to show a comma separated value of all the Keyword_Phrases (defined in the MetaKeyword table) that the page has.

Thanks for any suggestions.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-11-19 : 11:19:39

The subquery for the count is simple enough:
[code]
SELECT PI.*, (SELECT COUNT(*) FROM PageInfo_MetaKeyWord WHERE PageID = PI.PageID) AS MetaKeyWords FROM PageInfo PI
[code]
Building a CSV into a singleton SELECT would be more difficult.

Sarah Berger MCSD

settinUpShop
Starting Member

28 Posts

Posted - 2003-11-19 : 12:16:24

More difficult, but still possible? Just expand the sub query you created for the Count, with something that would return the queried values as a CSV. Maybe using a user defined function?

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-11-19 : 21:33:34

If you are using SQL 2000, then you can use UDFs. You can make one
like this:


CREATE FUNCTION GetWords (@Page INT)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Words varchar(8000)
SELECT @Words =  COALESCE(@Words + ', ', '') + Word
FROM Metakeywords WHERE ID IN(SELECT WordID FROM PageInfo_MetaKeywords WHERE PageID = @Page)
return @Words
end

and use it inline like


SELECT PI.*, Getwords(PI.ID) AS MetaKeyWords FROM PageInfo PI

Please note I have no clue if this'll work. I use SQL 7 and cannot use/test UDFs.

[/code]

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources