Conditional sql WHERE clause based on column values

Question

I have a table that stores users. Every user has an ID, a Name and an Access Level. The three possible Access Levels are Administrator, Manager and Simple User.

What I want is to conditionally select from this table based on the Access Level value. I demonstrate the logic bellow:

If user is Administrator, then select all users (Administrators, Managers, Simple Users)
Else if user is Manager select all Managers and all Simple Users
Else if user is Simple User select only himself

Is that possible?

Are the access levels all stored as strings, or are they numbers (foreign keys)? — StevieG
– StevieG, Commented Feb 7, 2012 at 12:12

user829237 · Accepted Answer · 2012-02-08 09:12:50Z

2

Providing Access Level is an integer that increases with the actual access level: Administrator = 3 Manager = 2 User = 1

Then

SELECT * FROM USERS
WHERE ACCESS_LEVEL <= (SELECT ACCESS_LEVEL FROM USERS WHERE ID = @ID)

And just switch the less than sign to greater than if it is opposite.

Applies to SQL-server

EDIT: To get what you want you can use something like this:

SELECT id,name FROM users where ID = @id
UNION DISTINCT
SELECT id, name from users where access_level <= 
    (select case when access_level = 1 then 0 else access_level end
    from users where id = @id)

With a query like this you will select all users at your current access_level or lower. Only exception if your access_level is 1 (e.g. normal user), then only your own user is selected.

edited Feb 8, 2012 at 9:12

answered Feb 7, 2012 at 12:13

user829237

1,7699 gold badges38 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tliokos Over a year ago

Thanks for the answer but i didn't make myself clear. Sorry just first post here. What i want i something like: SELECT id,name FROM users WHERE access_level <= (select access_level from users WHERE id=@id) AND {IF access_level = Administrator id = *} {ELSE IF access_level = Manager id = *} {ELSE IF access_level = Simple User id=(SELECT id FROM users WHERE id = @id)}

tliokos Over a year ago

The Only problem with the above logic is that a "Simple User" can see all other Simple users

Anthony Accioly Over a year ago

You can make user829237 solution work in MySQL by replacing end case with end. Example:

SET @id = 200; SELECT id, name FROM users WHERE ID = @id UNION DISTINCT SELECT id, name FROM users WHERE access_level <= (  SELECT     CASE       WHEN access_level = 1       THEN 0       ELSE access_level     END  FROM users  WHERE id = @id )

John Doyle · Accepted Answer · 2012-02-07 12:11:10Z

1

In Oracle the concept is known as Row Level Security. See articles such as this or this to get you started.

answered Feb 7, 2012 at 12:11

John Doyle

7,8235 gold badges35 silver badges40 bronze badges

Comments

Dhruvisha · Accepted Answer · 2012-02-07 12:15:23Z

1

You can use following code

If(access_level=='administrator'){   str query = 'select * from users';}else if(access_level=='manager'){    str query = 'select * from users where  access_level!="administrator"'; }else{   str query = 'select * from users where access_level="users"'; }

answered Feb 7, 2012 at 12:15

Dhruvisha

2,5301 gold badge22 silver badges31 bronze badges

1 Comment

tliokos Over a year ago

Unfortunately i'm using a Joomla component that doesn't allow me to use php logic. I have to do it in the select statement

Anthony Accioly · Accepted Answer · 2012-02-07 15:53:29Z

Might be a little overkill, but the following stored procedure can be used to achieve what you want:

DROP procedure IF EXISTS `listAccessibleUsers`;

DELIMITER $$
CREATE PROCEDURE `listAccessibleUsers`(IN user_id INT)
BEGIN
  DECLARE u_access_level INT;

  -- Select the user access level  
  SELECT access_level 
  FROM users
  WHERE users.id = user_id
  INTO u_access_level;

  -- Result for simple users
  IF u_access_level = 1 THEN
    SELECT id, name, access_level 
    FROM users
    WHERE users.id = user_id;
  -- Result for admistrators and managers
  ELSE
    SELECT id, name, access_level  
    FROM users
    WHERE access_level <= u_access_level;  
  END IF;
END$$

DELIMITER ;

Example calling code:

CALL listAccessibleUsers(200);

Collectives™ on Stack Overflow

Conditional sql WHERE clause based on column values

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related