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

我在

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,

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

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