1

I have three tables which contain an id column. I would like to join these three tables together to produce a single table which has the ids and their corresponding matches. E.g. table A:

id_A
1
2
4

table B:

id_B
1
2
3

table C:

id_C
1
3
4

Would produce:

id_A id_B id_C
1 1 1
2 2
4 4
3 3

I currently am using a full outer join:

SELECT * FROM A
FULL OUTER JOIN B ON id_A = id_B
FULL OUTER JOIN C ON id_A = id_C

But this means that table C would not join on anything in that is not in table A but is in table B, e.g. we end up with:

id_A id_B id_C
1 1 1
2 2
4 4
3
3
Barnaby Cooper
  • 43
  • 5
  • Does this answer your question? [How can I do a FULL OUTER JOIN in MySQL?](https://stackoverflow.com/questions/4796872/how-can-i-do-a-full-outer-join-in-mysql) – WOUNDEDStevenJones Aug 04 '22 at 13:59
  • https://www.db-fiddle.com/f/4qvPu7Db3TmB7pgVTk2ZBP/1 as a live example to tinker with. Emulating a FULL OUTER JOIN with 2 tables is straightforward, but adding the third table will take some more experimenting. – WOUNDEDStevenJones Aug 04 '22 at 14:27

1 Answers1

1

A UNION of these three queries works for your example:

select A.id_A, B.id_B, C.id_C
from A
left join B
  on A.id_A = B.id_B
left join C
  on A.id_A = C.id_C
union
select A.id_A, B.id_B, C.id_C
from B
left join A
  on B.id_B = A.id_A
left join C
  on B.id_B = C.id_C
union
select A.id_A, B.id_B, C.id_C
from C
left join A 
  on C.id_C = A.id_A
left join B
  on C.id_C = B.id_B

Output:

id_A id_B id_C
1 1 1
2 2
4 4
3 3
5
Isolated
  • 5,169
  • 1
  • 6
  • 18