EXISTS query help

Transact-SQL
1 
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

2

Perhaps you should use the reference from the outer table instead of joining on the CONTAINERS table again. When you join on the containers table, if any row in the table satisfies the condition, you will get TRUE for the exists clause

SELECT  S.trackingNumber ,
        C.containerCode ,
        C.containerName ,
        CASE WHEN EXISTS ( SELECT   *
                           FROM     [dbo].[ContainersContents] CC
                           WHERE 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;