RIGHT OUTER JOIN using MySQL 8.0.17 version

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);

Well, what happens if you tell it not to match on the contents, but on the actual links between the tables?

I’m sorry I don’t understand your question…

Right now, your query says “Give me every row in the t_par table, and every row in the t_contents table where the contents field matches the par_title field.”

And it dutifully gave you what you asked for.

Let me see if a can phrase this in a way that leads without giving…

Let’s take one paragraph. “Gaio Giulio Cesare”. What conditions of the contents table do i need to give, to get you all of the rows you desire, but no more than that?

Given these pieces of information:

| sID_c | sID_p | content_title | par_title               | sID |
+-------+-------+---------------+-------------------------+-----+
|     1 |     1 | Content 1     | - Gaio Giulio Cesare    |   1 |

What must be true in my join? Can you define a set of conditions that make this happen?

And if the answer is no, what would need to change in order for you to get the desired result?

Okay, thanks for suggestion.

I have tried this new version

mysql> SELECT DISTINCT
	t.sID,
	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
	LEFT JOIN `t_par` m ON t.contents = m.par_title 
ORDER BY
	t.sID ASC;
+-----+-------------------------+------------------------+------------------+------------------+-------------+-------------+
| sID | Contents                | par_title              | t_contents_sID_c | t_contents_sID_p | t_par_sID_c | t_par_sID_p |
+-----+-------------------------+------------------------+------------------+------------------+-------------+-------------+
|   1 | Content 1               | NULL                   |                1 | NULL             | NULL        | NULL        |
|   2 | - Gaio Giulio Cesare    | - Gaio Giulio Cesare   |                1 |                1 |           1 |           1 |
|   3 | Veni, vidi, vici.       | NULL                   |                1 | NULL             | NULL        | NULL        |
|   4 | Carpe diem.             | NULL                   |                1 | NULL             | NULL        | NULL        |
|   5 | Ubi maior minor cessat. | NULL                   |                1 | NULL             | NULL        | NULL        |
|   6 | - Quinto Orazio Flacco  | - Quinto Orazio Flacco |                1 |                2 |           1 |           2 |
|   7 | Condicio sine qua non.  | NULL                   |                1 | NULL             | NULL        | NULL        |
|   8 | Carthago delenda est.   | NULL                   |                1 | NULL             | NULL        | NULL        |
|   9 | Content 2               | NULL                   |                2 | NULL             | NULL        | NULL        |
|  10 | - Marco Porcio Catone   | - Marco Porcio Catone  |                2 |                1 |           2 |           1 |
|  11 | Inter sidera versor.    | NULL                   |                2 | NULL             | NULL        | NULL        |
+-----+-------------------------+------------------------+------------------+------------------+-------------+-------------+
11 rows in set (0.03 sec)

But I need this output

+-----+-------------------------+------------------------+------------------+------------------+-------------+-------------+
| sID | Contents                | par_title              | t_contents_sID_c | t_contents_sID_p | t_par_sID_c | t_par_sID_p |
+-----+-------------------------+------------------------+------------------+------------------+-------------+-------------+
|   1 | Content 1               | NULL                   |                1 | NULL             | NULL        | NULL        |
|   2 | - Gaio Giulio Cesare    | - Gaio Giulio Cesare   |                1 |                1 |           1 |           1 |
|   3 | Veni, vidi, vici.       | NULL                   |                1 | NULL             | NULL        | NULL        |
|   4 | Carpe diem.             | NULL                   |                1 | NULL             | NULL        | NULL        |
|   5 | Ubi maior minor cessat. | NULL                   |                1 | NULL             | NULL        | NULL        |
|   6 | - Quinto Orazio Flacco  | - Quinto Orazio Flacco |                1 |                2 |           1 |           2 |
|   7 | Condicio sine qua non.  | NULL                   |                1 | NULL             | NULL        | NULL        |
|   8 | Carthago delenda est.   | NULL                   |                1 | NULL             | NULL        | NULL        |
|   9 | Content 2               | NULL                   |                2 | NULL             | NULL        | NULL        |
|  10 | - Marco Porcio Catone   | - Marco Porcio Catone  |                2 |                1 |           2 |           1 |
|  11 | Inter sidera versor.    | NULL                   |                2 | NULL             | NULL        | NULL        |
| NULL| NULL                    | - Marco Tullio Cicerone| NULL             | NULL             |           3 |           1 |
| NULL| NULL                    | - Publilio Siro        | NULL             | NULL             |           4 |           1 |
| NULL| NULL                    | - Publio Ovidio Nasone | NULL             | NULL             |           5 |           1 |
| NULL| NULL                    | - Lucrezio             | NULL             | NULL             |           6 |           1 |
+-----+-------------------------+------------------------+------------------+------------------+-------------+-------------+

well, that didn’t seem to work

1 Like

K, let me try this.

+-------------------------+-------+-------+-----+
| Contents                | sID_c | sID_p | sID |
+-------------------------+-------+-------+-----+
| Veni, vidi, vici.       |     1 | NULL  |   3 |
+-------+-------+---------------+-------------------------+-----+
| 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 |
+-------+-------+---------------+-------------------------+-----+

With no other information than what I have provided here, can you tell me which paragraph that line belongs to, and why?

The row

+-------------------------+-------+-------+-----+
| Contents                | sID_c | sID_p | sID |
+-------------------------+-------+-------+-----+
| Veni, vidi, vici.       |     1 | NULL  |   3 |
+-------------------------+-------+-------+-----+

Belongs to Content 1 and paragraph - Gaio Giulio Cesare of Content 1.

I understand that the structure of these tables is not exactly linear but I can’t do anything. I’m just looking for help in solving my problem…

How do you make that declaration?
What are you looking at that defines that?

mysql> SELECT DISTINCT
	t.sID,
	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
	LEFT JOIN `t_par` m ON t.contents = m.par_title 
ORDER BY
	t.sID ASC;
+-----+-------------------------+------------------------+------------------+------------------+-------------+-------------+
| sID | Contents                | par_title              | t_contents_sID_c | t_contents_sID_p | t_par_sID_c | t_par_sID_p |
+-----+-------------------------+------------------------+------------------+------------------+-------------+-------------+
|   1 | Content 1               | NULL                   |                1 | NULL             | NULL        | NULL        |
|   2 | - Gaio Giulio Cesare    | - Gaio Giulio Cesare   |                1 |                1 |           1 |           1 |
|   3 | Veni, vidi, vici.       | NULL                   |                1 | NULL             | NULL        | NULL        |

That’s not what I said.
I said -

SELECT *  FROM
	(
	SELECT DISTINCT
		t.sID,
		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
		LEFT JOIN `t_par` m ON t.Contents = m.par_title UNION
	SELECT DISTINCT
		k.sID,
		k.Contents,
		g.par_title,
		k.sID_c AS t_contents_sID_c,
		k.sID_p AS t_contents_sID_p,
		g.sID_c AS t_par_sID_c,
		g.sID_p AS t_par_sID_p 
	FROM
		`t_contents` k
		RIGHT JOIN t_par g ON k.contents = g.par_title 
	) x 
ORDER BY
	ISNULL(x.sID) ASC, x.sID ASC;


+------+-------------------------+-------------------------+------------------+------------------+-------------+-------------+
| sID  | Contents                | par_title               | t_contents_sID_c | t_contents_sID_p | t_par_sID_c | t_par_sID_p |
+------+-------------------------+-------------------------+------------------+------------------+-------------+-------------+
|    1 | Content 1               | NULL                    |                1 | NULL             | NULL        | NULL        |
|    2 | - Gaio Giulio Cesare    | - Gaio Giulio Cesare    |                1 |                1 |           1 |           1 |
|    3 | Veni, vidi, vici.       | NULL                    |                1 | NULL             | NULL        | NULL        |
|    4 | Carpe diem.             | NULL                    |                1 | NULL             | NULL        | NULL        |
|    5 | Ubi maior minor cessat. | NULL                    |                1 | NULL             | NULL        | NULL        |
|    6 | - Quinto Orazio Flacco  | - Quinto Orazio Flacco  |                1 |                2 |           1 |           2 |
|    7 | Condicio sine qua non.  | NULL                    |                1 | NULL             | NULL        | NULL        |
|    8 | Carthago delenda est.   | NULL                    |                1 | NULL             | NULL        | NULL        |
|    9 | Content 2               | NULL                    |                2 | NULL             | NULL        | NULL        |
|   10 | - Marco Porcio Catone   | - Marco Porcio Catone   |                2 |                1 |           2 |           1 |
|   11 | Inter sidera versor.    | NULL                    |                2 | NULL             | NULL        | NULL        |
| NULL | NULL                    | - Lucrezio              | NULL             | NULL             |           6 |           1 |
| NULL | NULL                    | - Marco Tullio Cicerone | NULL             | NULL             |           3 |           1 |
| NULL | NULL                    | - Publilio Siro         | NULL             | NULL             |           4 |           1 |
| NULL | NULL                    | - Publio Ovidio Nasone  | NULL             | NULL             |           5 |           1 |
+------+-------------------------+-------------------------+------------------+------------------+-------------+-------------+
15 rows in set (0.04 sec)

Okay… Let me try… one… last thing.

What happens if I forget a line from a paragraph, and need to insert it later?

Add the line “Guido was here” to Paragraph 1 of Content 1, and then show me how you can associate it with paragraph 1.