Hello there,
I need to join two tables available on a remote MySql database 8.0.17 version, of which I’m not an administrator but only a guest.
This the table t_contents with contents , content number and paragraph number :
mysql> SELECT * FROM `t_contents`;
+-------------------------+-------+-------+-----+
| Contents | sID_c | sID_p | sID |
+-------------------------+-------+-------+-----+
| Content 1 | 1 | NULL | 1 |
| - Gaio Giulio Cesare | 1 | 1 | 2 |
| Veni, vidi, vici. | 1 | NULL | 3 |
| Carpe diem. | 1 | NULL | 4 |
| Ubi maior minor cessat. | 1 | NULL | 5 |
| - Quinto Orazio Flacco | 1 | 2 | 6 |
| Condicio sine qua non. | 1 | NULL | 7 |
| Carthago delenda est. | 1 | NULL | 8 |
| Content 2 | 2 | NULL | 9 |
| - Marco Porcio Catone | 2 | 1 | 10 |
| Inter sidera versor. | 2 | NULL | 11 |
+-------------------------+-------+-------+-----+
11 rows in set (0.03 sec)
It is important to know that the rows following the contents and paragraphs they can change in number…
This is the table t_par with contents and paragraphs and content number and paragraph number :
mysql> SELECT * FROM `t_par`;
+-------+-------+---------------+-------------------------+-----+
| sID_c | sID_p | content_title | par_title | sID |
+-------+-------+---------------+-------------------------+-----+
| 1 | 1 | Content 1 | - Gaio Giulio Cesare | 1 |
| 1 | 2 | Content 1 | - Quinto Orazio Flacco | 2 |
| 2 | 1 | Content 2 | - Marco Porcio Catone | 3 |
| 3 | 1 | Content 3 | - Marco Tullio Cicerone | 4 |
| 4 | 1 | Content 4 | - Publilio Siro | 5 |
| 5 | 1 | Content 5 | - Publio Ovidio Nasone | 6 |
| 6 | 1 | Content 6 | - Lucrezio | 7 |
+-------+-------+---------------+-------------------------+-----+
7 rows in set (0.02 sec)
On table t_par the number rows not change.
This is my query and return:
SELECT DISTINCT
t.Contents,
m.par_title,
t.sID_c AS t_contents_sID_c,
t.sID_p AS t_contents_sID_p,
m.sID_c AS t_par_sID_c,
m.sID_p AS t_par_sID_p
FROM
`t_contents` t
RIGHT OUTER JOIN `t_par` m ON t.contents = m.par_title;
+------------------------+-------------------------+------------------+------------------+-------------+-------------+
| Contents | par_title | t_contents_sID_c | t_contents_sID_p | t_par_sID_c | t_par_sID_p |
+------------------------+-------------------------+------------------+------------------+-------------+-------------+
| - Gaio Giulio Cesare | - Gaio Giulio Cesare | 1 | 1 | 1 | 1 |
| - Quinto Orazio Flacco | - Quinto Orazio Flacco | 1 | 2 | 1 | 2 |
| - Marco Porcio Catone | - Marco Porcio Catone | 2 | 1 | 2 | 1 |
| NULL | - Marco Tullio Cicerone | NULL | NULL | 3 | 1 |
| NULL | - Publilio Siro | NULL | NULL | 4 | 1 |
| NULL | - Publio Ovidio Nasone | NULL | NULL | 5 | 1 |
| NULL | - Lucrezio | NULL | NULL | 6 | 1 |
+------------------------+-------------------------+------------------+------------------+-------------+-------------+
7 rows in set (0.03 sec)
My question is there a way to extract paragraphs (for each contents) values from table t_contents using a sigle query?
desired output
+------------------------+-------------------------+-------------------------+
| Contents | par_title | par_contents |
+------------------------+-------------------------+-------------------------+
| - Gaio Giulio Cesare | - Gaio Giulio Cesare | Veni, vidi, vici. |
| - Gaio Giulio Cesare | - Gaio Giulio Cesare | Carpe diem. |
| - Gaio Giulio Cesare | - Gaio Giulio Cesare | Ubi maior minor cessat. |
| - Quinto Orazio Flacco | - Quinto Orazio Flacco | Condicio sine qua non. |
| - Quinto Orazio Flacco | - Quinto Orazio Flacco | Carthago delenda est. |
| - Marco Porcio Catone | - Marco Porcio Catone | Inter sidera versor. |
| NULL | - Marco Tullio Cicerone | NULL |
| NULL | - Publilio Siro | NULL |
| NULL | - Publio Ovidio Nasone | NULL |
| NULL | - Lucrezio | NULL |
+------------------------+-------------------------+-------------------------+
Thanks in advance for any help or suggestion.
Structure tables below
-- ----------------------------
-- Table structure for t_contents
-- ----------------------------
DROP TABLE IF EXISTS `t_contents`;
CREATE TABLE `t_contents` (
`Contents` varchar(255) NULL DEFAULT NULL,
`sID_c` int(11) NULL DEFAULT NULL,
`sID_p` int(11) NULL DEFAULT NULL,
`sID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sID`) USING BTREE
) ENGINE = InnoDB;
-- ----------------------------
-- Records of t_contents
-- ----------------------------
INSERT INTO `t_contents` VALUES ('Content 1', 1, NULL, 1);
INSERT INTO `t_contents` VALUES ('- Gaio Giulio Cesare', 1, 1, 2);
INSERT INTO `t_contents` VALUES ('Veni, vidi, vici.', 1, NULL, 3);
INSERT INTO `t_contents` VALUES ('Carpe diem.', 1, NULL, 4);
INSERT INTO `t_contents` VALUES ('Ubi maior minor cessat.', 1, NULL, 5);
INSERT INTO `t_contents` VALUES ('- Quinto Orazio Flacco', 1, 2, 6);
INSERT INTO `t_contents` VALUES ('Condicio sine qua non.', 1, NULL, 7);
INSERT INTO `t_contents` VALUES ('Carthago delenda est.', 1, NULL, 8);
INSERT INTO `t_contents` VALUES ('Content 2', 2, NULL, 9);
INSERT INTO `t_contents` VALUES ('- Marco Porcio Catone', 2, 1, 10);
INSERT INTO `t_contents` VALUES ('Inter sidera versor.', 2, NULL, 11);
-- ----------------------------
-- Table structure for t_par
-- ----------------------------
DROP TABLE IF EXISTS `t_par`;
CREATE TABLE `t_par` (
`sID_c` int(11) NULL DEFAULT NULL,
`sID_p` int(11) NULL DEFAULT NULL,
`content_title` varchar(255) NULL DEFAULT NULL,
`par_title` varchar(255) NULL DEFAULT NULL,
`sID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sID`) USING BTREE
) ENGINE = InnoDB;
-- ----------------------------
-- Records of t_par
-- ----------------------------
INSERT INTO `t_par` VALUES (1, 1, 'Content 1', '- Gaio Giulio Cesare', 1);
INSERT INTO `t_par` VALUES (1, 2, 'Content 1', '- Quinto Orazio Flacco', 2);
INSERT INTO `t_par` VALUES (2, 1, 'Content 2', '- Marco Porcio Catone', 3);
INSERT INTO `t_par` VALUES (3, 1, 'Content 3', '- Marco Tullio Cicerone', 4);
INSERT INTO `t_par` VALUES (4, 1, 'Content 4', '- Publilio Siro', 5);
INSERT INTO `t_par` VALUES (5, 1, 'Content 5', '- Publio Ovidio Nasone', 6);
INSERT INTO `t_par` VALUES (6, 1, 'Content 6', '- Lucrezio', 7);