Solved

Subquery to get multiple results?

Posted on 2006-07-06
7
408 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:jmpawson
  • 3
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17049563
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

0
 

Author Comment

by:jmpawson
ID: 17049620
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?
0
 

Expert Comment

by:scollado_psp
ID: 17049652
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.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17049656
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
0
 

Author Comment

by:jmpawson
ID: 17049686
Hmm, I thought that might be it... however it now gives: ERROR 156 - Incorrect syntax near the keyword 'return'
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17049740
missing )

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
0
 

Author Comment

by:jmpawson
ID: 17049772
Also uniqueidentified should be uniqueidentifier - But it works :)  

Now have to teach myself User Defined functions :0

Thanks for your help.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question