Solved

SQL subquery in the select list

Posted on 2004-03-30
6
1,479 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 42

Accepted Solution

by:
Eugene Z earned 125 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
Industry Leaders: 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 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 42

Expert Comment

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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