sqlserver复杂的关联查询

Question

A表中有字段
name id
user1 aaa
user2 bbb
user3 ccc
B表中有字段
ids
aaa,bbb
bbb,ccc
要实现根据B表的ids查询显示出user1，user2,这样的字样，该怎么写sql语句？
如：输入参数aaa,bbb，返回结果user1,user2

Answer 1

select name from A where find_in_set(id,'aaa,bbb')

Answer 2

create table #a(name varchar(10),id varchar(10))
insert into #a
select 'user1','aaa' union all
select 'user2','bbb' union all
select 'user3','ccc' 
create table #b(ids varchar(100))
insert into #b(ids)values('aaa,bbb'),('bbb,ccc')
declare @s varchar(10)='aaa'
select * from #b as b inner join #a as a on charindex(','+a.id,','+b.ids+',')>0
where a.id=@s

  ids    name    id

1 aaa,bbb user1 aaa