[SOLVED] How to get distinct column from the table and compare this column with other table in mysql

Issue

I have two tables, TABLE A AND TABLE B.

TABLE A:

TABLE B

|patient id|Session id|
|———-|———-|
|Pat1 |Sess1_P1 |
|Pat1 |Sess2_P1 |
|Pat1 |Sess3_P1 |
|Pat2 |Sess1_P2 |
|Pat2 |Sess2_P2 |
I want to get the resultant table to be like this by using mysql query.

Solution

SELECT DISTINCT tableA.session_id, tableB.session_id
FROM ( SELECT patient_id, session_id
       FROM tableA
       UNION 
       SELECT patient_id, session_id
       FROM tableB ) base
NATURAL LEFT JOIN tableA
NATURAL LEFT JOIN tableB

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1f026d8bdd6357f77af5cfee2cb062ba

Answered By – Akina

Answer Checked By – Mildred Charles (BugsFixing Admin)