Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: SQL Query How to..??

    I have data below and I can have several records for the same JobID but with different VRUConfirmation and JobType.

    Code:
    vruconfirmation jobtype jobid      
    --------------- ------- ---------- 
    NULL            +SV     0204042111     <-- LINE 01
    255071          BUD     0204042111   <-- LINE 02
    NULL            +SV     0204042222
    255111          BUD     0204042222
    NULL            +SV     0204042333
    NULL            +SV     0204042444
    255746          +SV     0204042555
    256745          BUD     0204042555
    How do I query it so that the list below comes out?
    Code:
    vruconfirmation jobtype jobid      
    --------------- ------- ---------- 
    255071          BUD     0204042111   <-- LINE 02
    255111          BUD     0204042222
    NULL            +SV     0204042333
    NULL            +SV     0204042444
    255746          +SV     0204042555
    256745          BUD     0204042555
    Basically, the list filters out records which has a NULL vruconfirmation and jobtype='+SV' and the same jobid exists but with jobtype='BUD'.

    Ex. Line 01 and Line 02 have the same JOBID but the query would only show LINE 02 and skip LINE 01.


    Any input will be appreciated.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18

    Re: SQL Query How to..??

    If I understand correctly what you're trying to do (filter out records where the vruconfirmation is null if the jobcode is repeated with a non-null vruconfirmation with a jobtype of BUD) then I think this will do what you wanted:

    (
    select vruconfirmation, jobtype, jobid
    from <<<table_name>>> as j1
    where jobid not in
    (
    select jobid from <<<table_name>>> as j2
    where jobtype='BUD' and vruconfirmation is not null
    )
    )
    union
    (
    select vruconfirmation, jobtype, jobid
    from <<<table_name>>> as j3
    where vruconfirmation is not null
    )
    order by jobid

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A non-union methdo:
    --------------------------
    select YourTable.*
    from YourTable
    left outer join YourTable BUDRecords
    on YourTable.jobid = BUDRecords.jobid
    and and YourTable.vruconfirmation is null
    and YourTable.jobtype = '+SV'
    and BUDRecords.jobtype = 'BUD'
    where BUDRecords.jobid is null
    --------------------------
    Pick whichever one suits you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Thanks both for your inputs. I will look into it.

Posting Permissions

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