Solved

Subquery to get multiple results?

Posted on 2006-07-06
7
409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

691 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