(mySQL) Unable to query 2 tables properly for data

Question

I have 2 tables. One is 'page_links' and the other is 'rpp'. Table page_links is the superset of table rpp.

The following is the schema of my tables:

-- Table structure for table `page_links`
--

CREATE TABLE IF NOT EXISTS `page_links` (
  `page` varchar(255) NOT NULL,
  `page_link` varchar(100) NOT NULL,
  `heading_id` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`page`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `page_links`
--

INSERT INTO `page_links` (`page`, `page_link`, `heading_id`) VALUES
('a1.php', 'A1', 8),
('b1.php', 'B1', 8),
('c1.php', 'C1', 5),
('d1.php', 'D1', 5),
('e1.php', 'E1', 8),
('f1.php', 'F1', 8),
('g1.php', 'G1', 8),
('h1.php', 'H1', 1),
('i1.php', 'I1', 1),
('j1.php', 'J1', 8),
('k1.php', 'K1', 8),
('l1.php', 'L1', 8),
('m1.php', 'M1', 8),
('n1.php', 'N1', 8),
('o1.php', 'O1', 8),
('p1.php', 'P1', 4),
('q1.php', 'Q1', 5),
('r1.php', 'R1', 4);


-- Table structure for table `rpp`
--

CREATE TABLE IF NOT EXISTS `rpp` (
  `role_id` tinyint(3) unsigned NOT NULL,
  `page` varchar(255) NOT NULL,
  `is_allowed` tinyint(1) NOT NULL,
  PRIMARY KEY  (`role_id`,`page`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `rpp`
--

INSERT INTO `rpp` (`role_id`, `page`, `is_allowed`) VALUES
(3, 'a1.php', 1),
(3, 'b1.php', 1),
(3, 'c1.php', 1),
(3, 'd1.php', 1),
(3, 'e1.php', 1),
(3, 'f1.php', 1),
(3, 'h1.php', 1),
(3, 'i1.php', 1),
(3, 'l1.php', 1),
(3, 'm1.php', 1),
(3, 'n1.php', 1),
(4, 'a1.php', 1),
(4, 'b1.php', 1),
(4, 'q1.php', 1),
(5, 'r1.php', 1);

WHAT I AM TRYING TO DO:

I am trying to query both the above tables (in a single query) in such a way that all the pages from page_links are displayed along with the is_allowed value from rpp for a particular role. For example, I want to get the is_allowed value of all the pages from rpp for role_id = 3 and at the same time, list all the available pages from page_links. A clear example of my expected result would be:

page      is_allowed    role_id
----------------------------------------
a1.php    1             3
b1.php    1             3
c1.php    1             3
d1.php    1             3
e1.php    1             3
f1.php    1             3
g1.php    NULL          NULL
h1.php    1             3
i1.php    1             3
j1.php    NULL          NULL
k1.php    NULL          NULL
l1.php    1             3
m1.php    1             3
n1.php    1             3
o1.php    NULL          NULL
p1.php    NULL          NULL
q1.php    NULL          NULL
r1.php    NULL          NULL

One more example of my desired result could be achieved by doing a LEFT JOIN rpp ON page_links.page = rpp.page but we need to omit using role_id = 3 (or any value) to be able to get that. But I do want to specify the role_id as well and get the results. I need the query to be able to get this result. I would appreciate any replies that could help me with this. If you can suggest me any changes as well to the table(s) design to be able to achieve the desired result, that's good as well. Thanks in advance.

Answer 1

Use:

   SELECT pl.page,
          r.is_allowed,
          r.role_id
     FROM PAGE_LINKS pl
LEFT JOIN RPP r ON r.page = pl.page
               AND OR r.role_id = 3

Previously:

   SELECT pl.page,
          r.is_allowed,
          r.role_id
     FROM PAGE_LINKS pl
LEFT JOIN RPP r ON r.page = pl.page
    WHERE r.role_id IS NULL OR r.role_id = 3

This query won't return pages that have a role_id other than 3 and that aren't null.

Answer 2

Try this:

SELECT page_links.page, is_allowed, role_id
FROM page_links
LEFT JOIN rpp
ON rpp.page = page_links.page AND rpp.role_id = 3

Answer 3

SELECT page_links.page, new_rpp.is_allowed, new_rpp.role_id
FROM page_links LEFT JOIN (SELECT * FROM rpp where role_id = 3) as new_rpp USING(page)

This will give you your desired results, it will not only show the pages that correspond to role_id = 3 but will also show all the pages that don't have a corresponding entry in rpp table for role_id = 3 So this should produce the result-set that you desire.