# Database Discussions > Microsoft SQL Server 2008 >  SQL Query - subquery problem

## Lava

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

----------


## skhanal

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

----------


## Lava

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

----------


## skhanal

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

----------


## Lava

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

----------


## rmiao

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

----------


## Lava

Hi -

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

----------


## rmiao

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

----------


## Lava

I will, thank you.

----------

