Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Unanswered: find most recent parentids where ... (was "Need Query Help in self join")


    I would need to do a query which I think is VERY TRICKY and yet possible to do using a self join.

    TABLE :

    ID Condition ConditionString Parent
    1 Policy Policy1 0
    2 Time 2005-02-02 1
    3 Rule Rule1 1
    4 Rule Rule2 1
    5 Policy Policy1 0
    6 Time 2005-01-02 5
    7 Rule Rule1 5
    8 Rule Rule2 5
    9 Policy Policy2 0
    10 Time 2005-02-02 9
    11 Rule Rule1 9
    12 Rule Rule2 9
    13 Policy Policy1 0
    14 Time 2005-02-02 13
    15 Rule Rule1 13

    The need is as follows:

    I would need to find those RECENT parent IDs (1 and 13) which had
    Main Condition as "Policy1" and sub condition has "Rule1".

    In the above case, I have 4 groups of records.
    1 with Policy1 and rules 1 & 2 entered on 2005-02-02

    13 with Policy1 and rule 1 entered on 2005-02-02

    5 doesn't fit in as it was entered on an earlier date.

    Likewise, 9 doesn't fit in as it has a different policy though the
    same rule.

    Is this possible in a single query? How do I do it?

    Thanks in advance

  2. #2
    Join Date
    Apr 2005
    Baltimore, MD
    You should be able to do this, but the more important question is why have you structured your database like this??? As soon as you noticed that you were storing dates/times in a string type column, alarms should have been going off. If you don't restructure your tables, then your query is going to be overly complex and inefficient. But if changing it is not a possibility, check out the date and time functions to cast your date strings and solve your ordering problem.

Posting Permissions

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