Using IN Statement with SQL 2005

Okay, not sure if I am being dumb here but.....

I have a column called Permissions and it contains the following rows..

1
1
31,42
1,39

The numbers in the 4 rows represent userid's.

What I need to do is be able to pull off any records from that column that have usrid 1 in it.

I have just tried.....

select * from documents where (permissions IN ('"&strUserid&"'))

However this ONLY gives me the first two rows not row 1,2 and 4.

Anyone out there handy with IN Statements? (or should I beusing anotehr statement for this?

Any help will be welcome.

 

You should try this:

select * from documents where (permissions IN ('"&"%"&strUserid&"%"&"'))

The % signs added at the beginning and the end of the strUserid variable tells the sql server that you want any rows that have the data in your variable, even if there is more data in the row, at the beginning of the element as well as at the end.

 

It looks like you're trying to do a many to 1 relationship through the use of strings which is inefficient. The best way is to create a join table.

A join table connects your two tables. Each record in the join table points to one record in your user table and one record in your permissions table.

Then when you want to get permissions for a user you just do the following

SELECT * FROM Permissions, UserPermissions WHERE Permissions.PermissionID = UserPermissions.PermissionID AND UserPermissions.UserID = 1

This way is much more efficient, and reduces the number of operations the DB has to do to compare against a string.

 

This is good then the original query, but the problem is if the column has 21 then it will return that row as well as it has 1, but I think the user is looking for an ID of 1 and not 1 in other IDs.

 

This might not work as the colum can have more than one ID seperated by commas.