Geek Answers Handbook

MySQL Select rows where the user has not yet voted (competitive application)

I have a photo contest app in which users can vote. I would like to select all contests in which the registered user has not yet voted.

So, I have two tables.

Competition table:

CREATE TABLE `contest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `desc` text NOT NULL,
  `created_date` datetime NOT NULL,
  `started_date` datetime NOT NULL,
  `nb_user_min` int(11) NOT NULL,
  `nb_photo_max` int(11) NOT NULL,
  `nb_photo_per_user` int(11) NOT NULL,
  `duration` int(11) DEFAULT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Table "contest_vote":

CREATE TABLE `contest_vote` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pic_id` int(11) DEFAULT NULL,
  `contest_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `ip` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

To make it clear, I want to get the number (or list) of contests in which the user has not yet voted. So I tried with LEFT JOIN, but it does not return a good set of results. Here it is my request so far:

SELECT DISTINCT c.id, c.title, cv.user_id
FROM contest c
LEFT JOIN contest_vote cv
ON cv.contest_id = c.id AND cv.user_id != ?
GROUP BY contest_id

("?" represents the user_id parameter).

Can you help me solve this problem?

+4
source share
2 answers

. , :

SELECT DISTINCT c.id, c.title
FROM contest c
WHERE c.id NOT IN (SELECT DISTINCT cv.contest_id FROM contest_vote cv WHERE cv.user_id = ?)
+2

:

SELECT DISTINCT c.id, c.title, cv.user_id
FROM contest c
LEFT JOIN contest_vote cv ON cv.contest_id = c.id AND cv.user_id != ? WHERE c.user_id = ?
GROUP BY contest_id
0

More articles:


All Articles