php + mysql (join?)

Question

Maybe this way it will make more sense.

MySQL "table"

Code:

id1 | id2
 1  |  2
 1  |  3
 2  |  4
 2  |  3
 3  |  4
 3  |  5

WHERE id1 = 1, this id is connected to 2 and 3: 1->2, 1->3

What I want to do is output the IDs of 2 and 3 which are NOT connected to 1, which in this particular case would be 4 and 5.

• 2->4 (1 is NOT connected to 4 = OK)

• 2->3 (1 is connected to 3 = NOT OK)

• 3->4 (1 is NOT connected to 4 = OK) ...but it should NOT be displayed twice, only because 2 and 3 are connected to 4!!

• 3->5 (1 is NOT connected to 5 = OK)

The only thing I could come up with would looks similar to the OHO code below, but I'd want to do all this within just one simple MySQL query, if possible (i.e. JOIN?):

$a = mysql_query("SELECT id2 FROM table WHERE id1 = 1");
while($b = mysql_fetch_assoc($a))
{
    $c = mysql_query("SELECT id2 FROM table WHERE id1 = $b[id2]");
    while($d = mysql_fetch_assoc($c))
    {
        $e = mysql_query("SELECT id2 FROM table WHERE id1 = 1 AND id2 = $d[id2]");
        $f = mysql_fetch_assoc($e);
        if(!$f['id2'])
        {
            echo $f['id2'];
        }
    }
}

note: One problem with the PHP code above is it would output 4 twice, because 2 and 3 are both connected to 4.

Answer 1

What about this?

select distinct a.id2
from myTable a
join (select id1, id2 from myTable where id1 = 1) b on a.id1 = b.id2
where NOT EXISTS (select 1 from myTable where id1 = b.id1)

Answer 2

What about SELECT DISTINCT id2 FROM table WHERE id1 != 1?

Answer 3

I may not understand your problem properly, but do you want something like this?

select
  distinct a.id2
from
  myTable a
where
  a.id2 not in (select id2 from myTable where id1 = 1);