asked on

Subquery to get multiple results?

I have two MS SQL Views:

VIEW_PSEARCH - records of peoples name and details (joined from various other tables). The key field is a GUID.
VIEW_CEC - this is a list of the persons Categories (the persons GUID, an Integer (Category number) and Text field (Category name)) (One to many relationship)

I want to use this query to produce a web page with a list of all the people and all the categories to which they belong e.g.

GUID, Name, Categories
{19C45320-BC8C-42A7-866C-10943AE6035F}, Bob Smith, (VIP, Regular Customer)
{9F8A55E6-54BC-4C10-9FAB-B75986793CF6}, Ann Other, (Occasional Customer, Local Person, Owns Dogs)

I know using a subquery will only produce a single result - so what sort of query should i use?

Thanks in advance.

Guy Hengel [angelIII / a3]

you have to create a "aggregation" function:

create function dbo.ConcatCategory(@guid uniqueidentified)
returns varchar(2000)
as
begin
declare @res varchar(2000)
set @res = ''

select @res = @res + ',' + CatName from VIEW_CEC WHERE guid = @guid

set @res = substring(@res, 2, length(@res - 1)
return (@res)
end

and use it like this

select *, dbo.ConcatCategory(guid) as categories
from VIEW_PSEARCH

jmpawson

ASKER

I understand the concept - wonderful. I've never created a function before, so this is exciting :)

I've pasted your code into the "Create New User Defined Function" dialogue in Enterprise Manager - but it comes up with the error:

Microsoft SQL-DMO ODBC SQLState: 42000
Error 195 'length' is not a recognized function name

Any ideas?

scollado_psp

u have to use LEN instead of length.
In the "aggregation" function u should declare a cursor to get all the categories of the person and concatenate them in the returning varchar.

Guy Hengel [angelIII / a3]

len instead of length, i am working too much with Oracle I gues

create function dbo.ConcatCategory(@guid uniqueidentified)
returns varchar(2000)
as
begin
declare @res varchar(2000)
set @res = ''

select @res = @res + ',' + CatName from VIEW_CEC WHERE guid = @guid

set @res = substring(@res, 2, len(@res - 1)
return (@res)
end

jmpawson

ASKER

Hmm, I thought that might be it... however it now gives: ERROR 156 - Incorrect syntax near the keyword 'return'

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmpawson

ASKER

Also uniqueidentified should be uniqueidentifier - But it works :)

Now have to teach myself User Defined functions :0

Thanks for your help.