How to calculate the number of total votes when there is two Posts tables?

Question

Here is the structure of my tables:

// table1
+----+-------+--------------------+
| id | color |      content       |
+----+-------+--------------------+
| 1  | blue  | it is a bad color  |
| 2  | red   | it is a good color |
| 3  | green | I don't like this  |
+----+-------+--------------------+

// table2
+----+-------+---------------------+
| id | site  |     description     |
+----+-------+---------------------+
| 1  | stack | help to programmers |
| 2  | google| everything you like |
+----+-------+---------------------+

// votes
+----+-----------+---------+-------+
| id | table_code| post_id | value |
+----+-----------+---------+-------+
| 1  |  1        | 1       |  1    |  // table1, post1, +1upvote     (blue)
| 2  |  1        | 2       | -1    |  // table1, post2, -1downvote   (red)
| 3  |  2        | 1       |  1    |  // table2, post1, +1upvote     (stack)
+----+-----------+---------+-------+

Also here is my query:

select t3.*, (select sum(value) from votes v where t3.id = v.post_id) total_votes
   from (
      select * from table1
        union all
      select * from table2
   ) t3

Here is my output:

+----+-------+---------------------+-------------+
| id | color |       content       | total_votes |
+----+-------+---------------------+-------------+
| 1  | blue  | it is a bad color   |  2          |  // Problem (it should be 1)
| 2  | red   | it is a good color  | -1          |
| 3  | green | I don't like this   |  0          |
| 1  | stack | help to programmers |  2          |  // Problem (it should be 1)
| 2  | google| everything you like |  0          |
+----+-------+---------------------+-------------+

As you see in the this ^ table, the calculation of total_votesis wrong. How can I fix it?

Note: According to reality, I can not combine table1 and table2. So, please don't tell me your structure is crazy.

Answer 1

You have to also specify table_code in the UNION:

select t3.*, 
       (select sum(value) 
        from votes v 
        where t3.id = v.post_id and 
              v.table_code = t3.table_code) total_votes
   from (
      select *, 1 table_code from table1
        union all
      select *, 2 from table2
   ) t3

Using table_code in the correlated sub-query we can pick the correct values from votes table.