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 |
CodePudding user response：
SELECT tableA.session_id, tableB.session_id
FROM ( SELECT patient_id, session_id
FROM tableA
UNION
SELECT patient_id, session_id
FROM tableB ) base
LEFT JOIN tableA USING (patient_id)
LEFT JOIN tableB USING (patient_id)
If (patient_id, session_id) is not unique - add DISTINCT.
CodePudding user response：
I would do it as I have been taught that it is very simple even though it has not been used for 10 years.
SELECT DISTINCT tableA.Session_id AS A,tableB.Session_id AS B FROM tableA,tableB WHERE tableA.patient_id=tableB.patient_id;
Here is a link to how it works : https://www.db-fiddle.com/f/3PnzHErrf2fZFGZY67K12X/65