Check whether no record for join condion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Check whether no record for join condion

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-20 : 01:49:32

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:14:33

do you mean this? if not,post some sample data and explain what you want with output


SELECT Batch.BatchHrs,
Hrs =
CASE
WHEN COUNT(Log.totalHrs) = 0
OR Log.TotalHrs = '00:00:00.0000000'
THEN MIN(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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources