We are in the process of developing

[varnix]

We are in the process of developing an internal app where we check a list contained in the DB that is a CSV list of user ID's that are allowed access to information on a particular resource.

So what we have is a field called 'authusers' that contains something like 1,5,3,2,12, etc - the order depends on when a user was authorized. With '1' being a catchall user for when we want everyone to be guaranteed access to it.

Database structure looks something like:
rname - resource name
rdesc - resource description
authusers - CSV list of authorized users
rcat - resource category

so I run a query such as:
select rcat,rname,rdesc from resources
where '1' in (authusers) or '#userid#' in (authusers)
and rcat like '4'

So with a userid of 3 and a test DB that contains two records where the authusers list is '1,3' and the rcat is '4' and another record where the authusers list is '3' and rcat is '4', I only get the lone record where the authusers is '3' and rcat is '4' returned.

What am I doing wrong in my query? I thought that the 'IN' statement allowed me query a CSV list within the DB.

Any suggestions?

 

[r937]

no, IN syntax allows you to query a CSV within the sql statement

you could use LIKE

... ','||authusers||',' LIKE '%,'||#userid#||',%'

you need to add the commas so that each term is separate, i.e. to prevent finding 5 in '2,17,45'

rudy