[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:

patient id Session id
Pat1 Sess1_P1
Pat1 Sess1_P1
Pat1 Sess1_P1
Pat1 Sess2_P1
Pat1 Sess2_P1
Pat1 Sess2_P1
Pat1 Sess4_P1
Pat1 Sess4_P1
Pat2 Sess1_P2
Pat2 Sess1_P2
Pat2 Sess2_P2
Pat2 Sess2_P2

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.

Session id Table A Session id Table B
Sess1_P1 Sess1_P1
Sess2_P1 Sess2_P1
Sess3_P1
Sess4_P1
Sess1_P2 Sess1_P2
Sess2_P2 Sess2_P2

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)

Leave a Reply

Your email address will not be published.