CSV Resultset for IN clause

1

Hello Experts,
I am working on writing a SQL query to get data from 2 tables with inner join and need some help. Here is the scenario. I have 2 tables say A and B.

table A
Name Value
1 Red
2 Green
3 Blue
4 Yellow
5 White

Table B

Name Value
Shirt 1,3
Shoe 1,2,4
Sock 4,5

Now, I am trying to write a query to join these 2 tables so that when I want to get data for shirt, I would retrieve Red and Blue.

I have tried the below but it is not taking the values to be list for the IN Clause
Test-1
SELECT Value FROM A WHERE Name IN

(
SELECT Value FROM B WHERE Name = 'Shirt'
)

Test-2
SELECT Value FROM A INNER JOIN B ON
A.Name IN B.Value
WHERE B.Name = 'Shirt'

Test-3
SELECT Value FROM A WHERE Name IN

(
SELECT CAST(Value AS VARCHAR(50)) FROM B WHERE Name = 'Shirt'
)

Any suggestion would be greatly appreciated

2

Welcome

Why have chosen this value column with comma delimited foreign keys approach? If this your design or something you have inherited?

Also what version of sql server are you on?

declare @colors table(name int, value varchar(50))

insert into @colors

select 1, 'Red' union
select 2, 'Green' union
select 3, 'Blue' union
select 4, 'Yellow' union
select 5, 'White' 


declare @goods table( name varchar(50), value varchar(50))

insert into @goods
select 'Shirt',	'1,3' union
select 'Shoe','1,2,4' union
select 'Sock', '4,5'


;with a
as
(
select name, b.value
  from @goods g
  cross apply string_split(g.value, ',') b
  where g.name =  'Shirt'
  )
  select c.* 
  from a
  join @colors c on a.value = c.name
3

this is a legacy application which I am trying to redesign. Earlier design was complicated and needed additional rows in table A. We use plain SQL to make a DB Query to get the results.

4

So is this the current design?