Help selecting rows based on values in other rows.

  • chris_rutherford

    Mr or Mrs. 500

    Points: 540

    I'm stuck. I have a table that I want to pull some info from that I don't know how to.

    There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column has several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').

    i.e example, this is the existing data.

    Call_id Call_Status

    555555 3 3RD RESPONDED

    235252 7 SEC RESOLVED

    555555 7 3RD RESOLVED

    325252 6 PENDING

    555555 6 PENDING

    325235 3 SEC RESPONDED

    555555 1 NEW

    This is the data I want...

    Call_id Call_Status

    555555 3 3RD RESPONDED

    555555 6 PENDING

    555555 7 3RD RESOLVED

    The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.

    Cheers,

    Chris

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    In the way I understood your problem, I believe that your result sould be some thing else.

    ("..I only want the 6 PENDING rows where..")

    Do you say the call_ID should have 6 PENDING and either 3 3RD RESPONDED or 7 3RD RESOLVED

    The way I unsterstand the problem this is the answer.

    Select Distinct A.Call_ID, A.Call_Status From (select * from Call_Master Where Call_Status ='6 PENDING') A,

    (select * from Call_Master Where Call_Status in ('3 3RD RESPONDED', '7 3RD RESOLVED')) B

    Where A.Call_ID = B.Call_ID

    If the problem is different explain me more.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • chris_rutherford

    Mr or Mrs. 500

    Points: 540

    Thanks to Preethi this job is now complete. I modified the code slightly to read the following...

    Select Distinct A.Call_ID,

    A.Customer_name,

    A.Call_Status_Value

    From (select * from status_tracking Where Call_Status_Value in ('3 3RD RESPONDED', '7 3RD RESOLVED','6 PENDING')) a,

    (select * from status_tracking Where Call_Status_Value in ('3 3RD RESPONDED', '7 3RD RESOLVED')) B

    Where A.Call_ID = B.Call_ID

    order by a.call_id asc

    Thanks again.

    Chris

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721419

    Thanks for posting and glad that it worked for you.

    Steve Jones

    steve@dkranch.net

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply