Select where val in(groups)

[Mark]

Hi - I have set-up security for my users - the security is held in a
text field, separated by a comma.

If the users a member of groups 1, 5 and 6 - the usergroups field is set
to 1,5,6 - to check if they are allowed access to the books, I need to
check each of these numbers (by using split,"," and building a SQL
statement - this needs to check against the membergroups of each book
title - which again is set as a text field - eg. "2,3,4,5"

The Select statement to show users book titles they are allowed to see
should be:

select id, titles, authors from tblbooks where (1 in (membergroups) OR 5
in (membergroups) or 6 in (membergroups))

...the error I get though is 'Syntax error converting the varchar value
'2,3,4,5' to a column of data type int.

If I change it to:

select id, titles, authors from tblbooks where ('1' in (membergroups) OR
'5' in (membergroups) or '6' in (membergroups))


...I don't get any errors, but I don't get any results either - any
ideas? Thanks a lot,

 

[Mark Schupp]

"best" solution involves restructuring your database to move the security
field into a table so that each code is by itself. Then you could use a
subselect with the IN operator to test.

I am going to guess that you will be unable to do that. In that case you
will have to use the LIKE operator as in

where (membergroups LIKE '%1%) or ...

Note that this will not work properly if codes are more than one digit
(above will match any code containing "1").

 

[Bob Barrows]

Hi - I have set-up security for my users - the security is held in a
text field, separated by a comma.

Bad! You are much better off normalizing this database design by using a
separate table in which the group numbers for each user are stored in
separate rows, like this:

UserID GroupID
1 1
1 5
1 6

If the users a member of groups 1, 5 and 6 - the usergroups field is
set to 1,5,6 - to check if they are allowed access to the books, I
need to check each of these numbers (by using split,"," and building
a SQL statement - this needs to check against the membergroups of
each book title - which again is set as a text field - eg. "2,3,4,5"

This really highlights why it is a bad idea to store multiple pieces of
information in a single field. Think about how easy this query would be if
you had the above structure:

WHERE ... GroupID IN (1,5,6)

The Select statement to show users book titles they are allowed to see
should be:

select id, titles, authors from tblbooks where (1 in (membergroups)
OR 5 in (membergroups) or 6 in (membergroups))

Why is user-security information stored in a table called "tblbooks"? Oh
wait! These are the groups that are allowed to view the books, right? So you
will need a table to store these groups as well! (can a user belong to more
than one group?) So a table such as the above example will work. Just change
"UserID" to "id" (my personal preference is to make these column names a
little more descriptive: BookID leaves no doubt about the data stored in the
column). The simple query would be:

.... FROM tblbooks b inner join BookGroups g
ON b.id = g.id
WHERE g.GroupID IN (1,5,6)

..the error I get though is 'Syntax error converting the varchar value
'2,3,4,5' to a column of data type int.

If I change it to:

select id, titles, authors from tblbooks where ('1' in (membergroups)
OR '5' in (membergroups) or '6' in (membergroups))


..I don't get any errors, but I don't get any results either - any
ideas? Thanks a lot,

The IN operator expects a list of values. It WILL NOT parse a column or
variable to turn it into a list, merely because the data in the variable or
column contains commas, making it appear to be a list. For one thing, you
may not wish it to do this.
So, here is what is happening when the comparison "('5' in (membergroups)"
is being evaluated: '5' is being compared to the entire string '2,3,4,5'.
This comparison will always return false: '5' will never equal '2,3,4,5'.


Your best approach is to normalize the database (see above). However, if you
cannot do this for some reason, then here are some possible solutions (these
solutions will not perform very well, but ...):

....WHERE membergroups LIKE '%1%' OR membergroups LIKE '%5%' OR membergroups
LIKE '%6%'

or

.... WHERE charindex('1',membergroups) > 0 OR ...

These will fail if you have any group numbers greater than 9, so:

....WHERE ',' + membergroups + ',' LIKE '%,1,%' OR ...

HTH,
Bob Barrows

 

[Bob Barrows]

Here is some more information about using lists and arrays with SQL Server:
http://www.algonet.se/~sommar/arrays-in-sql.html

HTH,
Bob Barrows

 

[Mark]

Hi - thank you both very much - it is not too late to change the
structure, and your recommendations have helped a lot.

Thanks again,

 

[Dan Brussee]

Hi - thank you both very much - it is not too late to change the
structure, and your recommendations have helped a lot.

Still seems a bit more complicated. You say you have 1 or more groups
that a member is in (1,5,6) and a list of items to match to (2,3,4,5).
The IN operator will not work here... you are trying to see if all
values of (1,5,6) appear in (2,3,4,5) - or if any of them are in the
2nd list, right?

Best way I can see is to have a table of User_Groups

UID GID
1 1
1 5
1 6

And a table of Book_Groups

BID GID
99 2
99 3
99 4
99 5
55 2

Then you should be able to do a query like...

Select DISTINCT b.ID FROM Books as b
INNER JOIN Book_Groups as bg ON bg.BID = b.ID
INNER JOIN User_Groups as ug ON ug.GID = bg.GID
INNER JOIN Users as u ON u.ID = ug.ID
WHERE u.ID = 1

This should return the book ID 99 using the data I showed above.
NOTE: Untested "air" code.