How to JOIN two table to get missing rows in the second table
In a simple voting system as
CREATE TABLE elections ( election_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255), CREATE TABLE votes ( election_id int(11), user_id int(11), FOREIGN KEYs
for getting the list of elections a user has voted, the following JOIN is used
SELECT * FROM elections JOIN votes USING(election_id) WHERE votes.user_id='x'
but how to get the list of elections a user has NOT voted?
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
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 INpredicate 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) );
SELECT * FROM elections WHERE election_id NOT IN ( SELECT DISTINCT(election_id) from votes );
As the accepted answer pulled elections where a specific voter did not vote, this doesn't really answer the OP's question. And, of course, it's just a minor tweak of one of the other answers, to get elections where no one voted. A comment to that effect might make this seem like a slightly better answer. Still, from the picture, pretty good for a monkey! :-)