Solved

SQL subquery in the select list

Posted on 2004-03-30
6
1,477 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

831 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