Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Compare 2 records in Same Table

SOLVED
Go to solution
intp
Frequent Advisor

Compare 2 records in Same Table

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
4 REPLIES
TwoProc
Honored Contributor

Re: Compare 2 records in Same Table

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');
We are the people our parents warned us about --Jimmy Buffett
James R. Ferguson
Acclaimed Contributor

Re: Compare 2 records in Same Table

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
Honored Contributor
Solution

Re: Compare 2 records in Same Table

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
Honored Contributor

Re: Compare 2 records in Same Table

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" )