MySQL Question

PHP/MySQL : How to distinct duplicated id from nested loop

So, I have 2 tables and I am trying to get an output like this.

//output that i need
SCvalue 22
SCvalue 23
Cvalue 17
SCvalue 24
SCvalue 25
Cvalue 19


My tables, the key 16 have 2 sub value at my table 2 the SCvalue 22, 33 as well as the key 18.

TABLE_1
| PID | criteria_name |
| 16 | Cvalue 16 |
| 17 | Cvalue 17 |
| 18 | Cvalue 18 |
| 19 | Cvalue 19 |



TABLE_2
| SID | PID | Sub_criteria_name |
| 22 | 16 | SCvalue 22 |
| 23 | 16 | SCvalue 23 |
| 24 | 18 | SCvalue 24 |
| 25 | 18 | SCvalue 25 |


I used nested loop to get that output that i need but.

//the output giving to me duplicates the value
SCvalue 22
SCvalue 23
SCvalue 24 <-- need to remove
SCvalue 25 <-- need to remove
Cvalue 17
SCvalue 22 <-- need to remove
SCvalue 23 <-- need to remove
SCvalue 24
SCvalue 25
Cvalue 19


My fail query

$sql = mysql_query("SELECT * from TABLE_1 where PID NOT IN(SELECT PID from TABLE_2)");
while($row = mysql_fetch_assoc($sql)){

$sql1 = mysql_query("SELECT distinct(SID), PID, Sub_criteria_name from TABLE_2");
while($row2 = mysql_fetch_assoc($sql1)){
echo $row2['Sub_criteria_name']."<br/>";
echo $row['criteria_name']."<br/>";
}
}

Answer

It seems like all you need is a single LEFT JOIN query:

SELECT COALESCE(t2.Sub_criteria_name, t1.criteria_name)
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t1.PID = t2.PID
ORDER BY t1.PID

Demo here

Comments