Link to home
Start Free TrialLog in
Avatar of gdspeare
gdspeare

asked on

SQL Query

I have a table that controls user security for an application.  Currently, if a user needs access to more than one department, I am entering multiple records for the user.  I would like to change that model to use just one row.  For example currently USER 1 might have two records in the security table.

USER                               DEPT
User 1                               010
User 1                               020

I would like to condense that into something more like:

USER                             DEPT
User 1                            '010','800'

and pull data based on one record, but am having difficulty.
declare @id varchar(255)
set @id = (SELECT dept from users where user_id = suser_sname())
 
print @id -- this pulls '010','800'
 
select * from test_budget
where dept IN ('010','800')-- this pulls the correct information
 
select * from test_budget 
where dept IN (@id) -- this returns nothing

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of reb73
reb73
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You should not do this. The DEPT column is normalized, if you put multiple values into the same row you violate the first normal form, it will only give you problems later.
Hi,

I second reb73 in that you should keep your current model.

But I think that if you move to the new model, the solution for you is to do this:

select * from test_budget where @id LIKE '%'' + dept  + '''%'

/peter