1

How would one carry out a full outer join on three tables?

I have already carried my join successfully on two tables, but I am clueless on how to add the third table to my query.

Here is my (long-winded) query:

SELECT MONTH(t1.datetime) AS month, COUNT(DISTINCT t1.column) AS t1Count, COUNT(DISTINCT t2.column) AS t2Count
FROM t1
LEFT OUTER JOIN t2 ON MONTH(t2.datetime) = MONTH(t1.datetime)
GROUP BY MONTH(t1.datetime)
UNION
SELECT MONTH(t1.datetime) AS month, COUNT(DISTINCT t1.column) AS t1Count, COUNT(DISTINCT t2.column) AS t2Count
FROM t1 
RIGHT OUTER JOIN t2 ON MONTH(t2.datetime) = MONTH(t1.datetime)
GROUP BY MONTH(t2.datetime)

Any guidance is much appreciated.

1 Answers1

4

Try this query -

(edited)

SELECT
  t.month,
  COUNT(DISTINCT t1.column) t1count,
  COUNT(DISTINCT t2.column) t2count,
  COUNT(DISTINCT t3.column) t3count
FROM 
  (SELECT MONTH(datetime) AS month FROM t1
    UNION
  SELECT MONTH(datetime) AS month FROM t2
    UNION
  SELECT MONTH(datetime) AS month FROM t3
  ) t
  LEFT JOIN t1
    ON t.month = MONTH(t1.datetime)
  LEFT JOIN t2
    ON t.month = MONTH(t2.datetime)
  LEFT JOIN t3
    ON t.month = MONTH(t3.datetime)
  GROUP BY
    month
Devart
  • 119,203
  • 23
  • 166
  • 186
  • Thank you for your suggestion. Unfortunately this returns a record for a single month (with correct count values for the three tables however), whereas I am seeking to display all month values. –  Feb 10 '12 at 13:16
  • 1
    I did not added 'GROUP BY month'. I have changed the answer, try it now;-) – Devart Feb 13 '12 at 08:08
  • 1
    My apologies - I should have noticed that was missing myself! This fits the bill perfectly, your help and insight is much appreciated. Thank you Devart. –  Feb 13 '12 at 08:41