Combining one column between two tables in MySQL

Question

This is a continuation of a question I asked last week. It concerns the combination of table columns from two different tables.

Please consider this example.

I have a table (vote_orders a) containing these values:

+----------------+---------------------+----------------+
| VOTE_CANDIDATE | RANKED_CHOICE_VOTES | ORIGINAL_VOTES |
+----------------+---------------------+----------------+
|              2 |                   4 |              0 |
|              3 |                   1 |              0 |
|              4 |                   2 |              0 |
|              5 |                   1 |              0 |
+----------------+---------------------+----------------+

SELECT vote_candidate, COUNT(*) ranked_choice_votes, 0 original_votes
FROM vote_orders a
INNER JOIN
(
  SELECT vote_id, MIN(vote_order) AS min_vote_order
  FROM vote_orders
  WHERE vote_candidate NOT IN (1,6)
  GROUP BY vote_id
) b
ON a.vote_id = b.vote_id
AND a.vote_order = b.min_vote_order
INNER JOIN
(
  SELECT vote_id
  FROM vote_orders
  WHERE vote_candidate = 1
  AND vote_order = 1
) c
ON a.vote_id = c.vote_id
GROUP BY vote_candidate;

I have another table (vote_orders) containing these values:

+----------------+
| ORIGINAL_VOTES |
+----------------+
|              1 |
|              2 |
|              4 |
|              2 |
+----------------+

SELECT COUNT(*) original_votes
FROM vote_orders
WHERE vote_order = 1
AND vote_candidate NOT IN (1,6)
GROUP BY vote_candidate;

Now I want to combine these two tables so that the resulting table looks like this (notice that ORIGINAL_VOTES now contains VOTE_ORDERS.ORIGINAL_VOTES [second queries] content):

+----------------+---------------------+----------------+
| VOTE_CANDIDATE | RANKED_CHOICE_VOTES | ORIGINAL_VOTES |
+----------------+---------------------+----------------+
|              2 |                   4 |              1 |
|              3 |                   1 |              2 |
|              4 |                   2 |              4 |
|              5 |                   1 |              2 |
+----------------+---------------------+----------------+

I guess that it requires an advanced combination of UNION and JOIN but I just can't get a grip on how to combine these to get this result. You can experiment with it in this fiddle. I would be deeply grateful for any ideas on how to solve this.

Answer 1

Ah, think I see what you wanted now.

Possibly just add another sub query that gets the number of primary votes for each candidate. Then LEFT OUTER JOIN this against the rest of your results ON the vote_candidate field.

SELECT a.vote_candidate, COUNT(*) ranked_choice_votes, d.original_votes
FROM vote_orders a
INNER JOIN
(
  SELECT vote_id, MIN(vote_order) AS min_vote_order
  FROM vote_orders
  WHERE vote_candidate NOT IN (1,6)
  GROUP BY vote_id
) b
ON a.vote_id = b.vote_id
AND a.vote_order = b.min_vote_order
INNER JOIN
(
  SELECT vote_id
  FROM vote_orders
  WHERE vote_candidate = 1
  AND vote_order = 1
) c
ON a.vote_id = c.vote_id
LEFT OUTER JOIN
(
    SELECT vote_candidate, COUNT(*) AS original_votes
    FROM vote_orders 
    WHERE vote_order = 1
    GROUP BY vote_candidate
) d
ON a.vote_candidate = d.vote_candidate
GROUP BY vote_candidate;