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

Question

所以，我有 2 个表，我正在尝试获得这样的输出。

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

我的表，键 16 在我的表 2 中有 2 个子值 SCvalue 22、33 以及键 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       |

我使用嵌套循环来获得我需要的输出。

//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

我的失败查询

$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 1

似乎您只需要一个LEFT JOIN 查询:

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