Query select all entries relative to a user When there are no "deny" entries relative to this object

Hi, i have a ACL table and i want to load in memory all the ACLs relative to the user, but i also want to EXCLUDE object ids where there is a "deny" entry.

my tables are as follow:
CREATE TABLE IF NOT EXISTS `led_acl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_cat` int(11) NOT NULL,
  `id_user` int(11) DEFAULT NULL,
  `id_group` int(11) DEFAULT NULL,
  `lvl` int(1) NOT NULL COMMENT '0 = deny, 1= read, 2= read/write, 3=read/write/delete',
  `type` int(4) DEFAULT NULL,
  `ext1` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO `led_acl` (`id`, `id_cat`, `id_user`, `id_group`, `lvl`, `type`, `ext1`) VALUES
(1, 7, 2, NULL, 1, 2, NULL),
(2, 6, NULL, 1, 1, NULL, NULL),
(3, 8, 2, NULL, 0, NULL, NULL),
(4, 7, NULL, 1, 0, NULL, NULL);


CREATE TABLE IF NOT EXISTS `led_group_membership` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL,
  `id_group` int(11) NOT NULL,
  `group_name` varchar(50) DEFAULT NULL,
  `ext2` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


INSERT INTO `led_group_membership` (`id`, `id_user`, `id_group`, `group_name`, `ext2`) VALUES
(1, 2, 1, NULL, NULL);

Open in new window


`id_cat` is the object_id in another hierarchical table (ADJACENT MODEL)
`id_user` is the ID of the USER or NULL if the entry is relative to a GROUP.
`id_group` is the ID of the GROUP (looking in the led_group_membership table) or NULL if the enrty is relative to a single USER.
`lvl` can be: '0 = deny, 1= read, 2= read/write, 3=read/write/delete'
`type` is reserved for future uses.


my query is as follow:

SELECT * FROM led_acl WHERE id_user = 2 AND lvl > 0 OR id_group IN (SELECT id_group FROM led_group_membership WHERE id_user=2) AND lvl > 0
AND id_cat NOT IN (SELECT id_cat FROM led_acl WHERE id_user = 2 AND lvl = 0 OR id_group IN (SELECT id_group FROM led_group_membership WHERE id_user=2) AND lvl = 0);

Open in new window


it is giving me the "wrong" result:
id       id_cat       id_user       id_group       lvl       type       ext1
1       7                    2       NULL                1       2               NULL
2       6                NULL       1                         1       NULL       NULL






My questin is as follow:
A) is there a more optimized way to do this?
B) in case NOT: WHY is it giving me the row with id_cat = 7 WHEN the second condition should EXCLUDE it?  I executed the second subquery and found it is right AFAIK...


Thanks.
LVL 10
ienaxxxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ienaxxxAuthor Commented:
OK, was a parenthesis error.
the right query is:

SELECT * FROM led_acl WHERE (id_user = 2 AND lvl > 0 OR id_group IN (SELECT id_group FROM led_group_membership WHERE id_user=2) AND lvl > 0)
AND id_cat NOT IN (SELECT id_cat FROM led_acl WHERE id_user = 2 AND lvl = 0 OR id_group IN (SELECT id_group FROM led_group_membership WHERE id_user=2) AND lvl = 0);

Open in new window


But Anyway i'm courious if there's an optimized way to do the same...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for ienaxxx's comment #a39517561

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.