Compare 2 records in Same Table

[intp]

Hi,

I have the following data in table A.
Acnt key , recrd type , ref accnt key
1000 , F, Null
2000 , F, Null
3000,N, 2000
4000 C 1000

i want to fetch records those Acnt key column not referred in
ref accnt key column of any other record whose type is 'N'. In above case i need to eliminate 2000,F,Null because 2000(accnt key) is referred in another record's 3000(accnt key) ref accnt key column whose record type is 'N'

Thanks

[TwoProc]

I've not tested this but just a
set operation should do the trick quickly.
If you work out the possible syntax errors, it should do the trick for you.

select tba.Acntkey
from tbla tba
minus
(select tbb.refaccntky as Acntkey
from tbla tbb
where tbb.recrdtype = 'N');

[James R. Ferguson]

Hi:

The Perl script below will accept piped input or will read a file of your data. It is assumed that your fields are comma-delimited, like:

1000,F,Null
2000,F,Null
3000,N,2000
4000,C,1000

# cat ./filter
#!/usr/bin/perl
use strict;
use warnings;
my %things;
my (@fld, @zot);
my $key;
while (<>) {
chomp;
@fld = split /,/, $_;
$things{$fld[0]} = $_;
push @zot, $fld[2] if $fld[1] =~/N/;
}
for $key (@zot) {
delete $things{$key};
}
for $key (sort keys %things) {
print "$things{$key}\n";
}
1;

...run as :

# ./filter datafile

(or):

# echo "1000,F,Null\n2000,F,Null\n3000,N,2000\n4000,C,1000" | ./filter

Regards!

...JRF...

[Hein van den Heuvel · Accepted solution]

Jrf, you know I like to suggest a perl solution for just about anything, but this problem seems to refers to a (relational) database table, asking for relational logic.
Just using sql would appear to be called for.

Intp, Are you sure you have your 'nots' and 'eliminate' versus 'fetch' straight in the problem description?

At any rate, it should be something along the lines of:

select * from a a where exists (select 'x' from a b where b.ref_key = a.acnt_key and b.type = 'N');'

Demo below.
Hth,
Hein.

create table A (acnt_key number(9), type char, ref_key number(9));
insert into a values (1000,'F',null);
:
:
insert into a values (4000,'C', 1000);

select * from a order by acnt_key

"ACNT_KEY","TYPE","REF_KEY"
"1000","F",""
"2000","F",""
"3000","N","2000"
"4000","C","1000"

select * from a a
where exists (
select 'x'
from a b
where a.acnt_key = b.ref_key
and b.type = 'N');'

ACNT_KEY TYPE REF_KEY
2000 F -

[Steve Lewis]

How about this single SQL:

select Acnt_key from table_A A
where not exists (
select 1 from table_A B
where B.ref_acnt_key = A.acnt_key
and B.recrd_type = "N" )