Showing combined results for graph from two tables [duplicate]

Question

I have two tables:

VOTES

ID |  YEARMONTH | VOTES
========================
1  | 201101     | 23
1  | 201102     | 12
1  | 201103     | 12
2  | 201101     | 15
3  | 201102     | 1
4  | 201102     | 17

LECTURES

ID |  YEARMONTH | LECTURES
========================
1  | 201101     | 1
2  | 201101     | 2
3  | 201102     | 5

Assuming that an ID can have no rows for each of the tables for a given yearmonth (either lectures or votes), I need to list the results so I can show them in a graph.

If for a yearmonth a result is missing on the other table, I need to display zero.

So that the result should look like this:

ID |  YEARMONTH | VOTES | LECTURES
==================================
1  | 201101     | 23    | 1
1  | 201102     | 12    | 0
1  | 201103     | 12    | 0

But of course, if VOTES were missing where LECTURES are not missing, I would need to display a 0 there.

Answer 1

One option is to create a subquery with all the possible ID/YearMonth combinations, and then LEFT JOIN on the other tables. Then use COALESCE to return 0 if the corresponding records are null:

SELECT t.ID, t.YearMonth, COALESCE(V.Votes,0) Votes, COALESCE(L.Lectures,0) Lectures
FROM (
    SELECT DISTINCT ID, YearMonth
    FROM Votes
    UNION 
    SELECT DISTINCT ID, YearMonth
    FROM Lectures 
    ) t
    LEFT JOIN Votes v on t.ID = v.ID AND t.YearMonth = v.YearMonth
    LEFT JOIN Lectures l on t.ID = l.ID AND t.YearMonth = l.YearMonth

SQL Fiddle Demo