How to JOIN two table to get missing rows in the second table
Use your existing query to get the opposite of the list you want. That list can then be checked against via NOT IN to get the desired list.
SELECT * FROM elections WHERE election_id NOT IN (
SELECT elections.election_id from elections
JOIN votes USING(election_id)
WHERE votes.user_id='x'
)
Use an outer join:
select e.election_id, e.title, v.user_id
from Elections e
LEFT OUTER JOIN votes v ON v.election_id = e.election_id and v.user_id = @userid
The UserId will be empty if no votes have been cast for a particular election, otherwise it will show up
If you only want to list the elections where there aren't any cast votes you might do it like this :
select *
from elections e
where election_id NOT IN
(select election_id
from votes
where user_id = @userid
)
There are a lot of ways to achieve what you are asking for. Perhaps the most straightforward way is to use a purely set-oriented approach:
select election_id from elections
minus -- except is used instead of minus by some vendors
select election_id from votes where user_id = ?
From the set of elections, we remove those where the user has voted. The result can be joined with elections to get the title of the elections. Even though you have not tagged your question, there is reason to believe that you are using MySQL, and MINUS or EXCEPT is not supported there.
Another variant is to use the NOT EXISTS
predicate:
select election_id, title
from elections e
where not exists (
select 1
from votes v
where e.election_id = v.election_id
and v.user_id = ?
);
I.e. the election where it does not exists a vote from the user. The NOT IN
predicate can be used in a similar fashion. Since there may be nulls involved it is worth noting that the semantics differs between IN and EXISTS.
Finally, you can use an outer join
select election_id, title
from elections e
left join votes v
on e.election_id = v.election_id
and v.user_id = ?
where v.user_id is null;
If there are no rows that match the ON predicate's, all columns from votes is replaced with null in the result. We can, therefore, check if any column from votes is null in the WHERE clause. Since both columns in votes may be null you need to be careful.
Ideally, you should fix your tables so that you don't have to deal with the gotchas caused by nulls:
CREATE TABLE elections
( election_id int NOT NULL AUTO_INCREMENT PRIMARY KEY
, title varchar(255) not null );
CREATE TABLE votes
( election_id int not null
, user_id int not null
, constraint pk_votes primary key (election_id, user_id)
, constraint fk_elections foreign key (election_id)
references elections (election_id)
);