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

Question

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

Is there an sqlfiddle?

Strawberry
– Strawberry

2016-09-05 16:56:12 +00:00
Commented Sep 5, 2016 at 16:56 — Strawberry
– Strawberry, Commented Sep 5, 2016 at 16:56

Giorgos Betsos · Accepted Answer · 2016-09-05 17:00:01Z

1

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

answered Sep 5, 2016 at 17:00

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related