php + mysql (join?)
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 i开发者_如何学God1 = 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.
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)
What about SELECT DISTINCT id2 FROM table WHERE id1 != 1
?
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);
精彩评论