(mySQL) Unable to query 2 tables properly for data
- by Devner
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.
