开发者

Non-existent tuples in a self-joined table

I'm playing with an algorithm to 'solve' activity networks - it's for a course in Project Management. The algorithm works on paper, but in order to solve it with a computer, I need a clever query which finds non-existent tuples in a self-joined table.

Let's say that the activities are as follows: A goes to B, A goes to C, B goes to D, C goes to D The table (called 'CPM') which I am using is populated thus:

+----+----------+--------+---------+
| ID | Fromnode | Tonode | Counter |
+----+----------+--------+---------+
|  1 | A        | B      | 0       |  
+----+----------+--------+---------+
|  2 | A        | C      | 0       | 
+----+----------+--------+---------+
|  3 | B        | D      | 0       | 
+----+----------+--------+---------+
|  4 | C        | D      | 0       | 
+----+----------+--------+---------+

I am trying to write a query which return all the 'fromnodes' which don't have a corresponding 'tonodes'. This is what I wrote

select id, fromnode
from cpm
where counter = 0
and not exists (select 'X'
from cpm cpm1
where tonode = cmp1.fromnode)

but this is returning all four tuples, not just the first two.

Queries using 'not exists' are hard enough to understand as it is, and the self join only makes things worse. What should be the query? BTW, I'm using Firebird, but I don't thi开发者_开发百科nk that that's the problem.

TIA, No'am


try an outer join: select a.id, a.fromnode from cpm as a left outer join cpm as b on a.fromnode = b.tonode where b.fromnode is null.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜