Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

Compare 2 records in Same Table

Go to solution
Frequent Advisor

Compare 2 records in Same Table


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'

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
(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


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:


# cat ./filter
use strict;
use warnings;
my %things;
my (@fld, @zot);
my $key;
while (<>) {
@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; as :

# ./filter datafile


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


Hein van den Heuvel
Honored Contributor

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.

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


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

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