Using Right JOIN in MySQL 8.0.17

Last Reply 14 days ago By dharmendr

Posted 14 days ago

Hi,

How to use RIGHT OUTER JOIN using MySQL 8.0.17 version

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 single 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.