Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Thumbs up Unanswered: Query Help: Sub Query and Joins in Single Table

    TABLE1:

    ID UniqueId DATE TIME Q2 Q3 Info1 Info2 Info3
    1 100 03/01/2011 17:40:37 1 11 NULL 9092892450 NULL
    2 100 03/01/2011 17:40:42 19 0 5000 NULL NULL
    3 100 03/01/2011 17:40:51 20 0 5000 NULL NULL
    4 100 03/01/2011 17:40:59 15 0 5000 NULL NULL
    5 100 03/01/2011 17:41:07 16 0 5000 NULL NULL
    6 100 03/01/2011 17:41:16 17 0 5000 NULL NULL
    7 100 03/01/2011 17:41:23 18 10 46 101 NULL
    8 100 03/01/2011 17:41:29 18 7 46 6 1
    9 101 03/02/2011 17:42:28 1 11 NULL 9940640699 NULL
    10 101 03/02/2011 17:42:40 20 0 5000 NULL NULL
    11 101 03/02/2011 17:42:45 1 1 1 1 1
    12 102 03/02/2011 17:42:58 1 11 NULL 9940540588 NULL
    13 102 03/02/2011 17:43:05 20 0 5000 NULL NULL
    14 102 03/02/2011 17:43:15 15 0 5000 NULL NULL
    15 102 03/02/2011 17:43:22 16 0 5000 NULL NULL
    17 102 03/02/2011 17:43:23 18 10 46 101 NULL
    18 102 03/02/2011 17:43:39 18 8 46 16 1


    Above is my sample table records. There you can find UniqueId column. It Reference each call transaction. For each Unique Id there may be Multiple columns. I need the output as single row for each Unique Id..

    Will explain how the Output should. I need 6 columns as my output.Column follows...
    UniqueID: Distinct Unique Id
    Date: Unique Date for each Unique Id
    Time: Time from Unique Id with Q3=11
    MDN: Info2 from UniqueId with Q3=11
    Status: Q3 from UniqueId (If Q3=1 Then 'NO ANSWER'.
    If Q3 IN (7,8) Then 'ANSWER'.
    )
    Duration: Info2 from uniqueId (If Q3=1 Info2 Value
    If Q3 IN (7,8) Info2 Value
    )

    Hence the Output Must be:

    UNIQUEID DATE TIME MDN STATUS DURATION
    100 03/01/2011 17:40:37 9092892450 ANSWER 6
    101 03/02/2011 17:42:28 9940640699 NO ANSWER 1
    102 03/02/2011 17:42:58 9940540588 ANSWER 16


    Please help in this to built a MYSQL Query ASAP. Its all comes from Sub Query within the table. It must be Distinct no repetition. Its sample records I will get 1000 of records per day. Please solve this Issue:
    Attached Thumbnails Attached Thumbnails Table and Output.JPG  
    Last edited by kalaiselvan328; 03-03-11 at 00:28.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try this:

    Code:
    SELECT a.uniqueid,
           a.sdate                             AS DATE,
           a.stime                             AS TIME,
           a.info2                             AS MDN,
           IF(b.q3 = 1, 'NO ANSWER', 'ANSWER') AS STATUS,
           b.info2                             AS DURATION
    FROM   {yourtable} a
           JOIN {yourtable} b
             ON ( a.uniqueid = b.uniqueid )
    WHERE  a.q3 = 11
           AND b.q3 IN ( 1, 7, 8 );
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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