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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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