SELECT S.trackingNumber,
C.containerCode,
C.containerName,
CASE WHEN EXISTS(SELECT *
FROM [dbo].[ContainersContents] CC
INNER JOIN [dbo].[Containers] C ON
C.containerSqlId = CC.containerSqlId AND
C.containerIncId = CC.containerIncId AND
C.containerTypeSqlId = @containerTypeContainerSqlId AND
C.containerTypeIncId = @containerTypeContainerIncId AND
CC.contentMetaTableSqlId = @specimenMetaTableSqlId AND
CC.contentMetaTableIncId = @specimenMetaTableIncId AND
CC.contentSqlId = SP.samplePartnerSqlId AND
CC.contentIncId = SP.samplePartnerIncId AND
C.isDeleted = 0 AND CC.isDeleted = 0)
THEN 'No' ELSE 'Yes' END AS IsEmpty,
SP.samplePartnerCode
FROM SHIPMENTS S
INNER JOIN CONTAINERS C ON S.containerIncId = C.containerIncId AND S.containerSqlId = C.containerSqlId AND S.isDeleted = 0 AND C.isDeleted = 0
INNER JOIN CONTAINERSCONTENTS CC ON C.containerIncId = CC.containerIncId AND C.containerSqlId = CC.containerSqlId AND CC.isDeleted = 0
INNER JOIN SamplesPartners SP ON SP.samplePartnerSqlId = CC.contentSqlId AND SP.samplePartnerIncId = CC.contentIncId AND SP.isDeleted = 0
WHERE CC.contentMetaTableSqlId = @specimenMetaTableSqlId AND
CC.contentMetaTableIncId = @specimenMetaTableIncId
I have the query posted here for returning the results where there is a column named isEmpty which shows the container is empty or not.
I have used the CASE clause and EXISTS keyword for this column.
But it fetches the value 'Yes' every time.
Is there any problem with the way I have used or any alternate way to do this? Thanking You