(mySQL) Cannot correctly query 2 tables for data

Question

I have 2 tables. One is page_links and the other is rpp. The page_links table is a superset of the rpp table.

Below is a diagram 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 TRY TO DO:

I am trying to query both of the above tables (in one query) so that all pages from page_links are displayed together with the is_allowed value from rpp for a specific role. For example, I want to get the is_allowed value of all pages from rpp for role_id = 3 and at the same time list all available pages from page_links. A striking 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

, LEFT JOIN rpp ON page_links.page = rpp.page, role_id = 3 ( ), . role_id . , . , . - , (-), , . .

Answer 1

:

   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

:

   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

role_id, 3 .

Answer 2

:

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 the desired results, it will not only display pages matching role_id = 3, but also show all pages that do not have a corresponding entry in the rpp table for role_id = 3 Therefore, this should produce the desired result.