Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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'
)
share|improve this answer
    
You should try to add a little more than just the SQL statement. It helps to describe how or why your answer works, or solves the problem at hand etc. –  Max Vernon Apr 5 '13 at 19:35
add comment

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
 )
share|improve this answer
    
The question is for a list of elections in which a specific user has not voted, not elections in which no votes have been cast. –  Sparr Apr 9 '13 at 0:14
    
Both scenarios are addressed in my answer, in each query respectively. The latter query lists elections with no votes cast by the given user –  druzin Apr 9 '13 at 11:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.