Solved

SQL subquery in the select list

Posted on 2004-03-30
6
1,483 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 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

688 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