Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Subquery to get multiple results?

Posted on 2006-07-06
7
Medium Priority
?
411 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 143

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 143

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 143

Accepted Solution

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

971 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