php – MySQL查询 – 连接两个表,产生重复的结果

发布时间：2020-12-13 17:13:55 所属栏目：PHP教程来源：网络整理

导读：我在 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`.`s

我在 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";

此查询由于某种原因返回重复记录.如果有人有任何见解,我将非常感激.

以下是两个表(挑战和验证)的结构：

挑战表：

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,`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;

感谢您的帮助,如果您需要更多信息,请告诉我们.

解决方法

你必须添加条件：

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";

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!