MySQL query - joining two tables producing duplicate results

mysql, php, sql

Solution

You have to add condition:

challenges.id = verify.challenge_id

to where clause as below

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

or using `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";

Problem

I'm running the following MySQL query in PHP. ``` "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"; ``` This query is returning duplicate records for some reason. If anyone has any insights, I would greatly appreciate it. Here is the structure for the two tables (challenges and verifications): Challenges Table: ``` 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 ; ``` Verifications Table: ``` 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; ``` Thanks for your help, and if you need any more info please let me know.

Original source

Related problems