CODEWITHSUNDEEP
sql
No'am Newman
No'am Newman

Reputation: 6477

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 think that that's the problem.

TIA, No'am

Upvotes: 1

Views: 144

Answers (1)

lijie
lijie

Reputation: 4871

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.

Upvotes: 2

Related Questions