Multiple lookup in SQL Server table

Question

Below is my two tables schema:

Table 1:

 id        int
 result   varchar

Table 2:

 id           int
 description  varchar

Table1 contains:

1 ; 1,4
2 ; 2
3 ; 2,3

Table2 contains:

1 : Bike
2 : Car
3 : Train
4 : Airplane

I now want to make a query which shows

1  Bike, Airplane
2  Car
3  Car, Train

How can I get the description?

Select t1.id, t2.description 
from t1, t2 
where..... ?

If t1.result has one value it's easy. But what must I do if there are more values separated by a comma?

Who can help?

Simple. Don't store anything as comma separated lists. That's just so bad in every way. — James Z, Jun 24 '16 at 15:19
Are you really storing your ids as CSV, or does your formatting only make it look this way? — Tim Biegeleisen, Jun 24 '16 at 15:21
Possible duplicate of [Joining a table based on comma separated values](http://stackoverflow.com/questions/26236436/joining-a-table-based-on-comma-separated-values) — Tab Alleman, Jun 24 '16 at 15:28
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Jun 24 '16 at 15:40

score 0 · Answer 1 · answered Jun 24 '16 at 17:48

Following is complete sql script for your desire results... Here I consider

CREATE TABLE [dbo].[t1](
    [id] [int] NULL,
    [Result] [varchar](50) NULL
)

as Table1 and

CREATE TABLE [dbo].[t2](
    [id] [int] NULL,
    [description] [nvarchar](50) NULL
)

as table2 and bellow is your complete sql server script.....

declare @Table table(Recid int identity, id int,Descp varchar(50))
create table #temp( id int,Descp varchar(50))
 insert into @Table
 select * from t1
declare @i int,@cnt int,@Str varchar(50),@Ids int,@StrQuery varchar(max)
select @cnt=count(*),@i=1 from @Table
 while @i<=@cnt
 begin
  select @str='(' + Descp +')',@Ids=id from @Table where Recid=@i
  set @StrQuery=' insert into #temp select ' +cast( @Ids as varchar(10)) + ', description from t2 where cast(id as varchar(10)) in ' + @str
  exec (@StrQuery)
  set @i=@i+1
 end
 declare @Table1 table(Recid int identity, id int,Descp varchar(50))
 insert into @Table1(id)
  select distinct id from #temp
  declare @Id int

  select @cnt=count(*),@i=1 from @Table1
while @i<=@cnt
begin
    select @Id=id from @Table1 where RecID =@i
    set @str =''
    SELECT  @str = COALESCE(@str + ',', '') + Descp 
    FROM #temp where id=@id
    update @Table1 set Descp =@str where id=@id
    set @i=@i+1
 end
  select id,RIGHT(RTRIM(Descp), LEN(Descp) - 1) from @Table1

There is really no need to use multiple loops for something like this. A string splitter would be a better choice. http://sqlperformance.com/2012/07/t-sql-queries/split-strings The best choice of course would be for the OP to fix their tables and follow 1NF. :) — Sean Lange, Jun 24 '16 at 18:10
I agree with you.A lots of way you can do yours works. Its one of these.. — Aditya Kumar Upadhyay, Jun 24 '16 at 18:16

Multiple lookup in SQL Server table

1 Answers1