How to exclude rows with double values in a SELECT statement

Question

I have a table that has amongst other things an ID column and a parent ID column:

id |parentID
------------
 1 |  1
 2 |  1
 3 |  3
 4 |  3
 5 |  5
 6 |  5

So basically there is an entry with id = parentID and some entry with an parentID which is an ID from some other row. Sometimes the entries with parentID <> id are missing, but id = parentID are always there. My goal is to find all those id = parentID entries that don't have the corresponding id <> parentID entry. How can it be done in Firebird 1.5? Thank you!

Answer 1

You do it just the same way you do with any other tables: left join the tables (this time, the table with itself) and check for null (missing rows).

See it on db<>fiddle here

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;

VERSION | RDB$CHARACTER_SET_NAME                                                                                                      
:------ | :---------------------------------------------------------------------------------------------------------------------------
3.0.5   | UTF8                                                                                                                        

create table pool( id integer primary key, parentID integer not null)

✓

insert into pool
select 1, 1 from rdb$database union all
select 2, 1 from rdb$database union all
select 3, 3 from rdb$database union all
select 4, 3 from rdb$database union all
select 5, 5 from rdb$database union all
select 6, 5 from rdb$database union all
select 7, 7 from rdb$database 

7 rows affected

select * from pool

ID | PARENTID
-: | -------:
 1 |        1
 2 |        1
 3 |        3
 4 |        3
 5 |        5
 6 |        5
 7 |        7

select p1.* from pool p1
left join pool p2
  on (p2.parentid = p1.parentid) and (p2.parentid <> p2.id)
where (p1.parentid = p1.id)
 and (p2.id is null)

ID | PARENTID
-: | -------:
 7 |        7

select p1.* from pool p1
left join pool p2
  on (p2.parentid = p1.id) and (p2.parentid <> p2.id)
where (p1.parentid = p1.id)
 and (p2.id is null)

ID | PARENTID
-: | -------:
 7 |        7