How to get a list of restricted_components with it's related information? I have the following tables structure (DDL):

CREATE TABLE restricted_components
(
    component_id INT(11) PRIMARY KEY NOT NULL,
    resources_id INT(11) NOT NULL,
    component_dom_id VARCHAR(50) NOT NULL,
    component_dom_class VARCHAR(50),
    component_dom_data_name VARCHAR(50),
    visible TINYINT(1) DEFAULT '0' NOT NULL
);

CREATE TABLE resources
(
    resources_id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    resources_name VARCHAR(64) NOT NULL,
    menu_href VARCHAR(128),
    menu_text VARCHAR(64) NOT NULL
);

CREATE TABLE acl_groups_to_resources
(
    groups_id INT(10) unsigned NOT NULL,
    resources_id INT(10) unsigned NOT NULL,
    bitmask SMALLINT(6) DEFAULT '0' NOT NULL,
    CONSTRAINT `PRIMARY` PRIMARY KEY (groups_id, resources_id)
);

CREATE TABLE groups
(
    groups_id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    groups_name VARCHAR(50) NOT NULL
);


I am trying to get a list of restricted_components with it's related information from the rest of the tables meaning the resources_name and groups_name.

I have made this query:

SELECT
  `rc`.*,
  `rs`.resources_name,
  `gr`.`groups_name`
FROM `restricted_components` AS `rc`
  LEFT JOIN `resources` AS `rs` ON rc.resources_id = rs.resources_id
  LEFT JOIN `acl_groups_to_resources` AS `acl_gr` ON rs.resources_id = acl_gr.resources_id
  LEFT JOIN `groups` AS `gr` ON acl_gr.groups_id = gr.groups_id


But is wrong since I have unwanted results as the image below shown:



I am sure the problem is on the LEFT JOIN but I can't find where exactly and how to fix this.

Update #1: Using JOIN instead doesn't work either.



I believe the problem is in the data but I am not sure at all, hopefully someone will have a time to check this.

Can I get any help?


  Note: I have leave a set of data here so you can play with it if needed but is not accurate, you'll need to remove some columns and it's values.
Playing with LEFT + INNER You can change logic of SQL query

If You need all from restricted_component - LEFT JOIN
But because groups must be for all - INNER

SELECT
  `rc`.*,
  `rs`.resources_name,
  `gr`.`groups_name`
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
ORDER BY component_id


result from Your dataset 

1   1   button_1    button_1        1   Users   Administrators
1   1   button_1    button_1        1   Users   User Admins
2   2   button_2            0   Companies   Administrators
3   3   button_3            0   Hardware    Administrators
4   4   button_4            0   Reports Administrators
4   4   button_4            0   Reports Reports Admin
5   5   button_5            0   Notifications   Administrators
5   5   button_5            0   Notifications   Company Admins