Showing combined results for graph from two tables

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.

score 1 · Answer 1 · edited May 23 '17 at 12:02

1

You would normally use a FULL OUTER JOIN for this, but MySQL doesn't support them.

You can emulate it with a UNION, however, as the accepted answer for this question shows.

edited May 23 '17 at 12:02

Community

1
1

answered Apr 02 '13 at 16:31

mwigdahl

16,268
7
50
64

score 1 · Accepted Answer · answered Apr 02 '13 at 16:32

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

answered Apr 02 '13 at 16:32

sgeddes

62,311
6
61
83

this seems a good approach, but the YEARMONTH is not linear. I need the YEARMONTH to be distinct to be able to use the data for the graph – Ted Apr 02 '13 at 21:37
@Ted -- I may need you to elaborate -- from your above data, what are your desired results? Are you not wanting t.Id in your results? Perhaps removing and then adding a SUM and/or GROUP BY can accomplish what you're looking for. – sgeddes Apr 02 '13 at 23:52
Eventually I used SUMs with GROUP BY as you suggested, thank you! – Ted Apr 07 '13 at 09:41

Showing combined results for graph from two tables

2 Answers2