MySQL 查询 - 连接两个表产生重复结果

Question

MySQL 查询 - 连接两个表产生重复结果

4

我正在PHP中运行以下MySQL查询。

"SELECT * 
FROM `challenges`,`verifications` 
WHERE (`challenges`.`user_id`='".$this->record['id']."' OR `challenges`.`opponent_id`='".$this->record['id']."') 
    AND `challenges`.`is_verified`='0' 
    AND (`challenges`.`status`='in-progress' OR `challenges`.`status`='pending') 
    AND 
    (
        (`verifications`.`user_id`='".$this->record['id']."' OR `verifications`.`opponent_id`='".$this->record['id']."') 
        AND (`verifications`.`user_verified`!=NULL AND `verifications`.`opponent_verified`=NULL) 
    ) 
LIMIT 100";

由于某些原因，此查询返回了重复记录。如果有任何见解，请告知，非常感谢。

以下是两个表(challenges和verifications)的结构：

Challenges表：

CREATE TABLE `challenges` (
  `id` int(11) NOT NULL auto_increment,
  `wager` int(11) NOT NULL,
  `type` varchar(255) NOT NULL,
  `user_id` int(11) NOT NULL,
  `opponent_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `start_time` time NOT NULL,
  `is_verified` tinyint(1) NOT NULL default '0',
  `status` varchar(255) NOT NULL default 'pending',
  `winner_id` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

验证表：

CREATE TABLE `verify` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `opponent_id` int(11) NOT NULL,
  `challenge_id` int(11) NOT NULL,
  `user_verified` int(11) default NULL,
  `opponent_verified` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `challenge_id` (`challenge_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;

感谢您的帮助，如果您需要更多信息，请告诉我。

- jjfrizzle

你应该避免使用 SELECT *（使用列列表）和隐式连接，因为它们都是不良实践。 - Kermit

@njk 为什么你认为 select * 是一种不好的习惯？ - John Dvorak

查询语句需要适当的格式化。 - John Dvorak

@JanDvorak 请查看这个链接。 - Kermit

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Robert · Accepted Answer

您需要添加条件：

challenges.id = verify.challenge_id

以下是where子句的用法：

"SELECT * 
 FROM `challenges`,`verifications` 
 WHERE `challenges`.`id` = `verify`.`challenge_id`
 AND (`challenges`.`user_id`='".$this->record['id']."' 
      OR `challenges`.`opponent_id`='".$this->record['id']."') 
 AND `challenges`.`is_verified`='0' 
 AND (`challenges`.`status`='in-progress' OR `challenges`.`status`='pending') 
 AND ( (`verifications`.`user_id`='".$this->record['id']."' 
         OR `verifications`.`opponent_id`='".$this->record['id']."') 
        AND (`verifications`.`user_verified`!=NULL 
              AND `verifications`.`opponent_verified`=NULL) 
     ) 
 LIMIT 100";

或者使用 ANSI-92。

"SELECT * 
 FROM `challenges` as `challenges`
 JOIN `verifications` as `verifications` on `challenges`.`id` = `verify`.`challenge_id`    
 WHERE (`challenges`.`user_id`='".$this->record['id']."' OR `challenges`.`opponent_id`='".$this->record['id']."') 
        AND `challenges`.`is_verified`='0' 
        AND (`challenges`.`status`='in-progress' OR `challenges`.`status`='pending') 
        AND 
        (
            (`verifications`.`user_id`='".$this->record['id']."' OR `verifications`.`opponent_id`='".$this->record['id']."') 
            AND (`verifications`.`user_verified`!=NULL AND `verifications`.`opponent_verified`=NULL) 
        ) 
        LIMIT 100";