?
Solved

SQL subquery in the select list

Posted on 2004-03-30
6
Medium Priority
?
1,486 Views
Last Modified: 2008-03-06
I'm trying to do an inner join between 2 tables but I don't want multiple records from the first table to be created because there are multiple records in the 2nd table.

My query as it stands:

Select m.sku, m.title, d.description from merch m
left join descriptions d on
d.sku = m.sku

This results in :
Sku     | title   | description
11111 | title1 | description 1
11111 | title1 | description 2
11111 | title1 | description 3
22222 | title2 | description 9
22222 | title2 | description 10
22222 | title2 | description 11
etc

What I want is one record for each sku and the descriptions in one column:
Sku     | title   | description
11111 | title1 | description 1, description2, description3
22222 | title2 | description 9, description10, description11

I have tried placing a select statement in the select clause like this:

Select m.sku, m.title, (select d.description from descriptions where d.sku = m.sku) as descriptions
from merch m
left join descriptions d on
d.sku = m.sku

But that only works when I use "select top 1" in the sub select. and even if it did work i wouldn't be able to add the comma between the descriptions.

Thanks
0
Comment
Question by:vertige
[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
6 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 10720272
if you have sql server 2000:

create  function udf_GroupDesc(@n int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''

select @str= case when  PATINDEX('%' + d.[description] + '%', @str) > 0 then
                 @str
             else          
                   @str + d.[description] + ', '
              end
              from merch m
      left join descriptions d
              on
      d.sku = m.sku
               where d.sku=@n
select @str = case datalength(@str) when 0 then '' else left(@str, datalength (@str)-2) end

return(@str)
end
 ----------------------

--Then run

Select m.sku, m.title,dbo.udf_GroupDesc(m.sku)from merch m
left join descriptions d on
d.sku = m.sku
group by m.sku, m.titale
0
 
LVL 11

Expert Comment

by:vc01778
ID: 10720282
Hello,

To get the desired result under SQLS 2K,  you need to create a function like this:

create function comma_sep ( @sku int) returns varchar(4000) as                  
begin                                                                            
  declare @result varchar(4000)                                                  
  set @result = ''                                                                
  select @result = @result                                                        
               + case when len(@result)>0 then ',' else '' end                    
               + description
    from descriptions                                                                      
    where sku = @sku                                                              
  return @result                                                                  
end        


... then the query wil be simply:


Select sku, title, dbo.comma_sep(sku) description from merch





VC
0
 
LVL 11

Expert Comment

by:vc01778
ID: 10720378
You can do the same with a cursor:

declare @result varchar(4000), @sku int, @title varchar(100)
declare c cursor for select sku from merch
create table #tmp(sku int, description varchar(4000))

open c
fetch next from c into @sku
while @@fetch_status = 0
begin
  set @result = ''                                                                
  select @result = @result                                                        
               + case when len(@result)>0 then ',' else '' end                    
               + description
  from descriptions                                                                      
  where sku = @sku
  insert into #tmp values(@sku, @result)
  fetch next from c into @sku
end
close c
deallocate c
select a.sku, title, description
from merch a join #tmp b on a.sku=b.sku

VC
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 2

Expert Comment

by:asterix_christian
ID: 10721149
Hello,
I have the same proble, and also the same solutions and the solutions are very good.
But I have the problem, that the table merch have differnt owners.

user.merch
dbo.merch
.
.
and so the function can not find the object merch with an ohter user.
What can we do ?
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 10722531
Hello asterix_christian,

Do one thing......

Post your questiong separatly...

Thanks,
V.Thandava Krishna.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 10723098
specify owner for tables that you need in function and in select stmnt
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

752 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