mysql不等于不工作

Question

我有一个如下所示的 SELECT 语句:

 SELECT * 
 FROM photos, p_votes 
 WHERE p_votes.p_id = photos.p_id 
   AND p_votes.u_id = 237

并返回我想要的结果，但我只想换一种方式:“我想要所有不像上面那样的结果”。所以我是这样做的:

 SELECT * 
 FROM photos, p_votes 
 WHERE p_votes.p_id != photos.p_id 
   AND p_votes.u_id != 237

但它没有返回任何结果。

这是我的插入代码:

$query = "UPDATE photos set p_up=p_up+1 where p_id=".$p_id;
  $result = $this->db->query($query);
  $this->load->helper('date');
  $now = time();
  $data_p_votes = array(
   'p_id' => $p_id,
   'u_id' => $this->session->userdata('u_id'),
   'pv_ip' => $ip,
   'pv_date' => unix_to_human($now, TRUE, 'eu')
  );
  $query_p_votes = $this->db->insert('p_votes', $data_p_votes);

我只想得到没有被用户投票的照片。

这是我的数据库结构和一些示例记录:

CREATE TABLE IF NOT EXISTS `photos` (
  `p_id` bigint(20) unsigned NOT NULL auto_increment,
  `u_id` bigint(20) unsigned NOT NULL default '0',
  `p_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `p_content` longtext NOT NULL,
  `p_title` text NOT NULL,
  `p_photo` text NOT NULL,
  `p_small` text NOT NULL,
  `p_thumb` text NOT NULL,
  `p_up` bigint(20) default '0',
  `p_down` bigint(20) default '0',
  PRIMARY KEY  (`p_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=260 ;

INSERT INTO `photos` (`p_id`, `u_id`, `p_date`, `p_content`, `p_title`, `p_photo`, `p_small`, `p_thumb`, `p_up`, `p_down`) VALUES
(255, 237, '2010-12-15 16:00:48', 'dock', 'dock', 'application/uploads/237/1292425240Dock.jpg', 'application/uploads/237/Jahorina-1292425240Dock.jpg', 'application/uploads/237/1292425240Dock_thumb.jpg', 1, 0),
(254, 237, '2010-12-15 16:00:23', 'desert', 'desert', 'application/uploads/237/1292425214Desert Landscape.jpg', 'application/uploads/237/Jahorina-1292425214Desert Landscape.jpg', 'application/uploads/237/1292425214Desert Landscape_thumb.jpg', 1, 0)

CREATE TABLE IF NOT EXISTS `p_votes` (
  `pv_id` bigint(20) unsigned NOT NULL auto_increment,
  `u_id` bigint(20) unsigned NOT NULL,
  `p_id` bigint(20) unsigned NOT NULL,
  `pv_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `pv_ip` varchar(200) NOT NULL,
  PRIMARY KEY  (`pv_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=235 ;


INSERT INTO `p_votes` (`pv_id`, `u_id`, `p_id`, `pv_date`, `pv_ip`) VALUES
(232, 237, 255, '2010-12-16 08:57:43', '85.146.204.228'),
(233, 237, 259, '2010-12-16 09:10:27', '85.146.204.228'),
(234, 237, 254, '2010-12-16 09:24:22', '85.146.204.228');

有什么建议吗？

Answer 1

鉴于数据集较小，您可以在第一个查询中使用 INNER JOIN 而不是使用交叉联接:

select *
from photos p
    inner join p_votes v on (p.p_id = v.p_id and v.u_id = 237);

并且您可以使用 LEFT JOIN 轻松找到您正在寻找的补码:

select *
from photos p
    left join p_votes v on (p.p_id = v.p_id and v.u_id = 237)
where v.p_id is null;

我尽量避免在像这样的简单语句中使用子查询(对@FatherStorm 抱歉)。