Results 1 to 9 of 9

Thread: SQL Query - subquery problem

  1. #1
    Join Date
    Dec 2002
    Posts
    50

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    If PKID is different for each transaction what uniquely identifies a person?

  3. #3
    Join Date
    Dec 2002
    Posts
    50
    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

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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

  5. #5
    Join Date
    Dec 2002
    Posts
    50
    I ran your code, I got missing keyword. Is there a ) missing?

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    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

  7. #7
    Join Date
    Dec 2002
    Posts
    50
    Hi -

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

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    It's different from t-sql, you should post it in Oracle forum.

  9. #9
    Join Date
    Dec 2002
    Posts
    50
    I will, thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •