Mysql – How to get only one acl_groups_to_resources.bitmask for a given set of parameters

join;MySQL

I have the following tables in my DB:

CREATE TABLE `restricted_components` (
  `component_id` int(11) NOT NULL,
  `resources_id` int(11) NOT NULL,
  `component_dom_id` varchar(50) NOT NULL,
  `component_dom_data_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`component_id`),
  UNIQUE KEY `component_id_UNIQUE` (`component_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `resources` (
  `resources_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `resources_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `menu_href` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `menu_text` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`resources_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `acl_groups_to_resources` (
  `groups_id` int(10) unsigned NOT NULL,
  `resources_id` int(10) unsigned NOT NULL,
  `bitmask` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`groups_id`,`resources_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `groups` (
  `groups_id` int(11) NOT NULL AUTO_INCREMENT,
  `groups_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`groups_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `users_to_groups` (
  `users_id` int(11) NOT NULL,
  `groups_id` int(11) NOT NULL,
  PRIMARY KEY (`users_id`,`groups_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `users` (
  `users_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_users_id` int(11) DEFAULT '0',
  `firstname` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`users_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I need to get the unique acl_groups_to_resources.bitmask value having the restricted_components.component_dom_id, resources.menu_href, users.users_id, [restricted_components.component_dom_data_name] (this last one is optional see the OR on the SQL).

Coming from this post and tweaking it a bit I end up with the following SQL:

SELECT
  `rc`.`component_id`,
  `acl_gr`.`resources_id`,
  `acl_gr`.`bitmask`
FROM `restricted_components` AS `rc`
  LEFT JOIN `resources` AS `rs` ON rc.resources_id = rs.resources_id
  INNER JOIN `acl_groups_to_resources` AS `acl_gr` ON rs.resources_id = acl_gr.resources_id
  INNER JOIN `groups` AS `gr` ON acl_gr.groups_id = gr.groups_id
  INNER JOIN `users_to_groups` AS `usr_gr` ON gr.groups_id = usr_gr.groups_id
  INNER JOIN `users` AS `usr` ON usr_gr.users_id = usr.users_id
WHERE (rc.component_dom_id = 'add_component_restriction')
  AND (rs.menu_href = 'adminconsole')
  AND (usr.users_id = 976)
  OR  (rc.component_dom_data_name = 'btn_add_restriction')

The query above works but I am getting two results instead of one:

component_id | resources_id | bitmask
--------------------------------------
1            | 129          | 1
1            | 129          | 15

A correct output would be a unique row containing the bitmask value for the given parameters. Something is wrong on the query but I am not sure what it's so I need more eyes on this.

This is the current data on each table (maybe the problem is on the data):

restricted_components
=====================

component_id | resources_id | component_dom_id         |  component_dom_data_name
----------------------------------------------------------------------------------
1            | 129          | add_component_restriction  
2            | 129          | edit_component_restriction
3            | 129          | delete_component_restriction

resources
=====================

resources_id | resources_name | menu_href    | menu_text
-------------------------------------------------------------
129          | Add            | adminconsole | Admin Console

acl_groups_to_resources
=====================

groups_id | resources_id | bitmask | created_by_users_id
--------------------------------------------------------
1         | 129          | 1       | 976
2         | 129          | 15      | 976
3         | 129          | 0       | 976

groups
=====================

groups_id | groups_name
---------------------------
1         | Administrators
2         | Restricted Admin
3         | Others

users_to_groups
======================

users_id | groups_id
---------------------------
976      | 1
976      | 2

users
=====================

users_id | parent_users_id | firstname | email
-------------------------------------------------------------
976      | 0               | Dev       | userdev@email.comdev

Can any give me some help on this? (I am using MySQL 5.5)

Note: Feel free to rewrite the SQL if needed in order to obtain the required results.

Best Answer

JOINs tend to get more and more rows.

You probably need to combine the 'dup' rows and use BIT_OR() to shrink back to one row:

SELECT   `rc`.`component_id`,
         `acl_gr`.`resources_id`,
         BIT_OR(`acl_gr`.`bitmask`)  AS bitmask
    FROM 
         ...  -- as you have now
    GROUP BY component_id, resources_id