SQL Query - subquery problem

Hi -

I have a two tables, table person p and charges c

When I write below SQL statement:

select p.PKID, b.lastname, b.firstname, c.chcode from Person P, charges c
where p.PKID= c.PKIDand
p.lastname = 'ABC' and p.firstname = 'XYZ'

I got:

326 ABC XYZ CHRG07M1
326 ABC XYZ CHRG235
326 ABC XYZ TTSS1111
326 ABC XYZ DDE1111
605 ABC XYZ CHRG123
916 ABC XYZ GRK456
405 ABC XYZ CHRG456
474 ABC XYZ CHRG123
557 ABC XYZ SSS345
943 ABC XYZ MM4567
943 ABC XYZ CHRG123
943 ABC XYZ UTM121
217 ABC XYZ MER345
217 ABC XYZ DES3237M1

as you see, PKID is different for the same person as each time data was entered for that person, a new value was generated.

What I would like to do is to filter the records from person and charges table where charge codes = CHRG123 and CHRG456 AND FLAG anyone who has extra Charges. In above case, this person has both charges but also got extra charges. So the goal is to filter anyone who has charges chrg123 and chrg 345 and flag if the person has a sum > than 2 when chcode in (chrg123, chrg345)

I hope I am clear.

Thanks,
Lava

If PKID is different for each transaction what uniquely identifies a person?

There is no way you can find someone uniquely! The SYSID is the primary key in the person table, one person can have more than a sysid. In one example I sent you, you see there are many sysids for the ABC person.

Lava

Ok, then your only choice is assuming two records with same last name and first name is for the same person.

select p.lastname, p.firstname, c.chcode from Person P join charges c
on p.PKID= c.PKID
join
(select p1.lastname, p1.firstname
from Person p1 join charges c
on p1.PKID= c.PKID
where c.chcode in ('chrg123', 'chrg345')
group by p1.lastname, p1.firstname
having count(*) > 2) as aa
on p.lastname = aa.lastname and p.firstname = aa.firstname

I ran your code, I got missing keyword. Is there a ) missing?

The statement doesn't miss ')', may try following:

select p.lastname, p.firstname, c.chcode from Person P join charges c
on p.PKID= c.PKID
join
(select p1.lastname, p1.firstname
from Person p1 join charges c1
on p1.PKID= c1.PKID
where c1.chcode in ('chrg123', 'chrg345')
group by p1.lastname, p1.firstname
having count(*) > 2) as aa
on p.lastname = aa.lastname and p.firstname = aa.firstname

Hi -

I am getting missing keyword, could it be because I ran the command using Oracle SQL developer?

It's different from t-sql, you should post it in Oracle forum.

I will, thank you.