Solved

SQL subquery in the select list

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

Accepted Solution

by:
EugeneZ 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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:EugeneZ
ID: 10723098
specify owner for tables that you need in function and in select stmnt
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now