0

I have 3 tables like below.

T1

id, count
1, 10
2, 20
3, 30

T2

id, status
1, 100
2, 200
3, 300
4, 400

T3

id, refresh
2, 2000
3, 3000

I want to convert all these table's 2nd columns into one result set.

id, count, status, refresh
1,   10,   100,    NULL
2,   20,   200,    2000
3,   30,   300,    3000
4,   NULL, 400,    NULL

Im not sure still full outer join supports this format. Any better way to do this?

Answer:

Since its closed, so posting the answer here:

SELECT COALESCE(T1.id, T2.id , T3.id), T1.count,T2.status,T3.refresh
FROM T1 
LEFT OUTER JOIN T2  USING (id)
LEFT OUTER JOIN T3  USING (id)
UNION 
SELECT COALESCE(T1.id, T2.id , T3.id), T1.count,T2.status,T3.refresh
FROM T2
LEFT OUTER JOIN T1  USING (id)
LEFT OUTER JOIN T3  USING (id)
UNION 
SELECT COALESCE(T1.id, T2.id , T3.id), T1.count,T2.status,T3.refresh
FROM T3 
LEFT OUTER JOIN T1  USING (id)
LEFT OUTER JOIN T2  USING (id)
TheDataGuy
  • 2,712
  • 6
  • 37
  • 89
  • 1
    This is exactly what full outer join is for. Unfortunately, MySQL doesn't have it. I've linked to a question that says how to emulate it. You just have to do it multiple times because you have more than 2 tables. – Barmar Sep 14 '22 at 15:47
  • in your case this has nothing to do with fulloouter join as long as you know that T" has all id you can make https://dbfiddle.uk/5d0N_wHq only when you know that all the tables have different id you need a full outer join – nbk Sep 14 '22 at 15:50
  • It worked like a charm, but on T3 when I have one more row like `5, 5000`, its not showing – TheDataGuy Sep 14 '22 at 16:05
  • use a Right JOIN ON T3, or you must implemented the full join as Barmar posted also don't Forget `COALAESCE$(T1.id,T3.id)`to get all ids – nbk Sep 14 '22 at 16:16

0 Answers0