SELF JOIN with count condition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SELF JOIN with count condition

  1. #1
    Join Date
    Mar 2008
    Posts
    2

    SELF JOIN with count condition

    I am requesting sql self join ideas from the experts.

    PROC_ID UID PROC_TIMESTAMP
    -----------------------------------------------------------------------
    706 1 2007-02-06 14:41:12.29
    706 2 2007-02-06 17:31:02.191
    706 3 2007-02-27 11:42:31.961
    901 1 2008-02-29 14:29:19.264
    902 2 2008-02-29 14:36:58.62
    902 2 2008-02-29 14:37:31.388

    PROC_ID = 706 dentotes the record with UID = 1,2,3 should be processed
    PROC_ID = 901 dentotes the record with UID = 1 was processed successfully
    PROC_ID = 902 dentotes the record with UID = 2 was processed with ERROR

    I am looking for a query that filters out the SUCCESSFULLY processed record with UID 1 and the records that has been processed with ERROR 'n' number of times.
    Here the 'n' = TWO TIMES.

    According to this test case the query should output following record with UID = 3
    706 3 2007-02-27 11:42:31.961

    Please can anyone show me SQL query.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by skm
    Please can anyone show me SQL query.
    ...and, where are you so far? Could you show us your best effort?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    I am looking for a query that filters out the SUCCESSFULLY processed record with UID 1 and the records that has been processed with ERROR 'n' number of times.
    Here the 'n' = TWO TIMES.
    Could you please explain how / why you are saying UID 1 is successfully processed and UID 3 is not successfully processed.

    Regards

    Raj

  4. #4
    Join Date
    Mar 2008
    Posts
    2
    Quote Originally Posted by s.rajaram
    Could you please explain how / why you are saying UID 1 is successfully processed and UID 3 is not successfully processed.
    This table would have intially records with MSG_ID as 706 or 704(Sorry I missed this in my original email). The query is interested in selecting records of UID where MSG_ID is 706. Please assume that the meta data associated MSG_ID=706 says that the record needs to be processed by an arbitrary application (remember that the value of UID is important for the application).

    When the application processes the record for a UID value it may succeed or fail.

    The idea that I came up is to have another MSG_ID to denote and understand contextually that a record with UID is processed successfully, so MSG_ID = 901. Similarly, MSG_ID = 902 denotes that a record with UID was failed when processed. I also have to consider the fact that the query does not select the UID that has failed 'n' number of times let say 2 times.

    So the query should Select the UID for whom MSG_ID = only 704 and that UID is NOT processed successfully lets say 2 times.

    From the example following needs to be processed:
    MSG_ID UID
    706 1
    706 2
    706 3

    Here UID=1 is successfully processed so it has a record with (901 1 2008-02-29 14:29:19.264) so it should not be selected.

    Here UID=2 was processed with error so it has 2 records with
    (902 2 2008-02-29 14:36:58.62 and 902 2 2008-02-29 14:37:31.388) so it should not be selected. Here I have considered that a UID can be attempted MAX number of times i.e. 2 times.

    Here UID=3 is not processed yet. This is the record this query must only pick.

    Please use following test data: (introduced PROC_ID=704 that might be present in the table)
    PROC_ID UID PROC_TIMESTAMP
    -----------------------------------------------------------------------
    704 1 2007-02-05 12:01:10.40
    706 1 2007-02-06 14:41:12.29
    706 2 2007-02-06 17:31:02.191
    706 3 2007-02-27 11:42:31.961
    901 1 2008-02-29 14:29:19.264
    902 2 2008-02-29 14:36:58.62
    902 2 2008-02-29 14:37:31.388

Posting Permissions

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


Click Here to Expand Forum to Full Width