Query puzzle: Same table query comparison of two fields

[shyamal]

I have the following fields in the security table:
I would like to return all sc_adp_nums where sc_underly_adp_num has a matching sc_symbol. In this example, I would like 000361105 returned as the sc_symbol matches with the sc_underly_adp_num that 000361GFC has.

sc_adp_num sc_symbol sc_underly_adp_num
------------- ------------ ------------------
000360206 AAON
000361105 AIR
000361GFC AIRGF AIR
000369348 FLXT
12486QLHC .648815108 .648815108
18799WPLP AOOPL AOOPL

I ran the following query:
select sc_adp_num, sc_symbol from security
where sc_underly_adp_num = sc_symbol which resulted in this:
This is not correct because it is an exact match:-

sc_adp_num sc_symbol sc_underly_adp_num
------------- ------------ ------------------
12486QLHC .648815108 .648815108
18799WPLP AOOPL AOOPL

Thanks in Advance.
S.Leonard

 

[Guest_imported]

I dont have the slaitless idiea of what your talking about

 

[MikeJones]

You would need to join the table to itself...

Try...

select a.sc_adp_num, b.sc_symbol
from security a, security b
where a.sc_underly_adp_num = b.sc_symbol
and a.sc_adp_num != b.sc_adp_num

 

[shyamal]

Thanks! This was a bit tricky.