Finding voters that did not vote in an election through a many to many relationship with MySQL

Question

I have two tables linked through a many-to-any relationship. Table one contains all voters with an ID, table two contains all elections with an ID, and table three links both tables using their IDs.

Not all voters voted in all elections. I would like to query the many-to-many relationship to find the elections for each voter that they did not vote in. I'm using MySQL.

Answer 1

A typical solution to this is to generate all possible combinations of voters and elections with a cross join, then try to bring the junction table with a left join: where no record matches, you know a voter missed an election.

Consider:

select v.voter_id, e.election_id
from voters v
cross join elections e
left join voter_elections ve 
    on  ve.voter_id = v.voter_id
    and ve.election_id = e.election_id
where ve.voter_id is null