SQL Query How To..?

• ARPRINCE

  SSC-Addicted

  Points: 466

  More actions

  February 16, 2004 at 1:32 pm

  #161627

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

  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?

  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

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 16, 2004 at 2:03 pm

  #494246

  Try:

  SELECT T.vruconfirmation,T.jobtype,T.jobid

   FROM ttt T

  JOIN(

   SELECT  jobid, Count(*)as Cnt

   FROM ttt

   GROUP BY jobid) Q ON  Q.jobid = T.JOBID

  WHERE Cnt = 1 OR (Cnt > 1 and vruconfirmation IS NOT NULL)

  ORDER BY T.jobid

  
  * Noel

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  February 16, 2004 at 2:10 pm

  #494249

  Basically a self join where outer table (GetNonNulls) WILL have a record

  for any jobid recs that have a none NULL data rec with same jobid.

  Then uses the vruconfirmation value from this outer table.

  SELECT DISTINCT GetNonNulls.vruconfirmation as vruconfirmation, GetAll.jobtype, GetAll.jobid

  FROM [TableName] GetAll

  LEFT OUTER JOIN [TableName] GetNonNulls

      ON GetAll.jobid = GetNonNulls.jobid

      AND GetNonNulls.vruconfirmation IS NOT NULL

  Notes: Not tested against real data.

  May not be the most efficient because of DISTINCT

  May be better with a pair of UNIONed "self joins".

  Hope this helps

  
  
  Once you understand the BITs, all the pieces come together

• ARPRINCE

  SSC-Addicted

  Points: 466

  More actions

  February 17, 2004 at 7:58 am

  #494359

  Thank you both for your inputs. I will look into it.