How to select rows from a table based on a column value in another table that has multiple values?

Question

I have two tables.

User with columns UserId, Area

The Area column is populated with multiple comma separated values (from listbox) - data looks like this:

User1 KA,TN,AP,GJ
User2 MH,UP,MP,GJ

Order with columns OrderID, ProductID, Qty, Area

Data looks like this:

1 Prod1 10 GJ
2 Prod1 22 MH
3 Prod2 3  AP
4 Prod2 77 TN

How to select rows from Order table based on User table?

Say logged in user is User1. His areas are KA,TN,AP,GJ.

The result should look like this:

Prod1 10 GJ
Prod2 77 TN

Please advise on the SQL query to get this result.

Thanks and regards Krishna

It'd be a lot easier if your data was normalized. Why are you storing multiple values as one string blob in a single field? — David, Dec 27 '16 at 12:21
Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Dec 27 '16 at 12:24
Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** — Zohar Peled, Dec 27 '16 at 12:47
why the 3rd record is missing in the result set ? Area 'AP' is also linked to 'User1'. — Abdul Rasheed, Dec 27 '16 at 12:47

score 1 · Accepted Answer · answered Dec 27 '16 at 13:03

Don't set this solution as a good practice because as all comments said above:
Never, ever store data as comma separated items.

Workaround:-

Manipulate the values that selected form user table for getting a good structure via using Replace function as next demo:-

Create table #user (username varchar(10), Areas varchar (100))
go
insert into #user values ('User1', 'KA,TN,AP,GJ')
insert into #user values ('User2', 'MH,UP,MP,GJ')
go
Create table #order ( OrderID int , ProductID varchar(10), Qty int , Area char (2))
go
insert into #order values (1, 'Prod1', 10, 'GJ')
insert into #order values (2, 'Prod1', 22, 'MH')
insert into #order values (3, 'Prod2', 3,  'AP')
insert into #order values (4, 'Prod2', 77, 'TN')
go 

declare @List nvarchar(250)

select @List = '('''+(select Areas from #user where username = 'User1') + ''')'
select @List = replace(@List,',',''',''')


exec ('select ProductID,Qty, area from #order
where Area in' +  @List )

drop table #user
drop table #order

Result:-

Note: I assumed the 3rd record is missing in your desired result set by mistake.

@nkrishna Glad to help you, bro :) – ahmed abdelqader Dec 27 '16 at 13:40 — ahmed abdelqader, Dec 27 '16 at 13:40

score 0 · Answer 2 · answered Dec 27 '16 at 13:53

0

you can use patindex

select o.*
from u
join o on patindex('%,'+o.Area+',%', ','+u.area+',')>0
where userid= 'User1'

answered Dec 27 '16 at 13:53

MtwStark

3,866
1
18
32

score 0 · Answer 3 · answered Dec 28 '16 at 03:55

Store data as comma separated items is not a good practice. But you can try something like below

DECLARE @V_USER NVARCHAR(100)   ='User1'

SELECT  *
FROM    Order   AS  O
    INNER JOIN User AS U    ON U.Area LIKE '%'+O.Area+'%'
WHERE   U.UserId    =   @V_USER

How to select rows from a table based on a column value in another table that has multiple values?

3 Answers3