Check whether no record on join condion

• Babu Kumarasamy

  SSC Rookie

  Points: 38

  More actions

  March 20, 2009 at 12:26 am

  #128812

  Hi,

  My requirement is that if there is no record in Log Table for the Batch Table Join Condition Log.ID = Batch.ID AND Log.LogDT = Batch.BatchDT, Then we have to get the Batch.BatchValue when

  COUNT(Log.totalHrs) = 0

  We have to check whether this is null or empty i.e. there is no record for the condition in Log table. This is thing I need.

  else if there is record for Id and the BatchDT we get the min of Log.LogValue

  totalHrs DataType is Time

  SELECT Batch.BatchHrs,

  Hrs =

  CASE

  WHEN COUNT(Log.totalHrs) = 0

  OR Log.TotalHrs = '00:00:00.0000000'

  THEN Batch.BatchValue

  ELSE MIN(Log.LogValue)

  END

  FROM Batch

  LEFT JOIN Log

  ON Log.ID = Batch.ID

  AND Log.LogDT = Batch.BatchDT

  WHERE Batch.ID = '01'

  AND Batch.BatchDT = '3/20/2009'

  GROUP BY Batch.BatchHr

  Thanks,

  Babu Kumarasamy

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  March 20, 2009 at 12:42 am

  #963335

  Look up the EXISTS clause.

  IF EXISTS (your query here)

  BEGIN

  Do something

  END

  "Keep Trying"