grouping and conditional select

[ssruprai]

Hello,

 

I have two tables:

 

1. users - just stores names.

2. user_rights - stores different rights users has. I am using right_level to determine what a user can and cannot do say 10 is lowest right and 100 is highest.

 

 

Tables can be created and populated using this sql:

 

DROP TABLE IF EXISTS `user_rights`;
CREATE TABLE IF NOT EXISTS `user_rights` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned default NULL,
  `right_level` int(10) unsigned default NULL,
  `active` tinyint(1) unsigned default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `user_rights` (`id`, `user_id`, `right_level`, `active`) VALUES (1, 1, 10, 1), (2, 1, 20, 0), (3, 2, 10, 1), (4, 2, 20, 1), (5, 2, 30, 1), (6, 4, 40, 0), (7, 3, 10, 1), (8, 3, 20, 1), (9, 3, 30, 1), (10, 3, 40, 0), (14, 3, 50, 0), (12, 1, 30, 1), (13, 1, 40, 0);


DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `first_name` varchar(25) NOT NULL default '',
  `last_name` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `first_name`, `last_name`) VALUES (1, 'User1', 'bb'), (2, 'User2', 'cc'), (3, 'User3', 'dd');

 

 

I am creating a web page where a user having a certain right is trying to access a page where rights of other users are displayed.

At login user selects which right_level he wants to use.

 

When a user logs in and tries to access a page which displays rights, I need records returned:

 

1. Where the HIGHEST right_level for a certain user is LESS THAN the right_level of the user accessing the page. (This result is produced using query below)

 

OR

 

2. Where the HIGHEST right_level for a certain user is GREATER THAN OR EQUAL to the currently logged in user's right_level but that GREATER THAN OR EQUALright level must be active = 0

 

I use the following query and it seems to do fine except that it doesn't handle the 2 above

 

select u.id as user_id, max(r.right_level), u.first_name, u.last_name, group_concat(r.right_level,"^^^",r.active separator ";") as all_rights from users u inner join user_rights r on u.id=r.user_id
group by u.id having max(r.right_level) < 40

 

Here I am assuming the right level of the currently logged in user is 40

 

 

Thanks,

Sukhwinder Singh

[fenway]

Sorry, I don't follow.