Results 1 to 3 of 3

Thread: Database Query

  1. #1
    Join Date
    Jul 2009

    Angry Unanswered: Database Query

    Hi all,
    This is wrecking my head. I have a table from which I wish to return those records having payments in one year but not in another e.g. 2009 but not in 2010
    Assume a payments received table (subs) as follows
    Cus_id c6
    Cus_payment decimal
    Acc_year Int

    OROU01 10.00 2008
    OROU01 10.00 2009
    OROU01 10.00 2009
    OROU01 10.00 2010

    JAME01 10.00 2008
    JAME01 10.00 2008
    JAME01 10.00 2009
    JAME01 10.00 2010

    BROW01 10.00 2008
    BROW01 13.00 2008
    BROW01 11.00 2009
    BROW01 13.00 2009 (No 2010 payments so this is the record I want to returned.)

    I am currently using this the following to create a temporary table from which I can make a comparison between the SUBSTEMP and SUBS tables to get the non payors. There has to be a better more refined way.

    SELECT DISTINCT e.cus_id into subsTemp
    FROM subs AS e LEFT JOIN subs AS m ON e.cus_id = m.cus_id
    WHERE e.acc_year = 2009 AND m.acc_year = 2010

    Any help appreciated


  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    SELECT DISTINCT e.cus_id
    FROM subs AS e
    WHERE NOT EXISTS (SELECT * FROM subs AS m WHERE e.cus_id = m.cus_id AND m.acc_year = 2010)
    AND e.acc_year  = 2009
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009

    Database Query

    Thanks... I had tried a number of sub queries.
    You come near Ireland I owe you a drink!!!!!!!


Posting Permissions

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