CODEWITHSUNDEEP
sqlsql-serversql-server-2008
CodingEE
CodingEE

Reputation: 323

Return all records and display message if record is NULL

I am working with content from our distributor, and their database files are not the best in the world. So, I am doing the best I can do, with what I have.

I have two tables, HLItems and HLImages.

I need to return ALL products from HLItems. And for the rows that do not exist, I need to display a message.

This is what I have tried, but it is not working. It returns all the records even the NULL rows, are getting 'Front Cover' displayed.

Front Cover is what is displayed in the record if the record exists. Else, nothing.

With RIGHT OUTER JOIN 

SELECT    
    (CASE 
        WHEN EXISTS (SELECT TOP 1 HLImages.Caption 
                     FROM HLImages  
                     RIGHT OUTER JOIN HLItems ON HLImages.ItemNo = HLItems.ItemNo) 
           THEN 'Front Cover'
           ELSE 'No Cover' 
     END) AS thumbnail, 
    Title, ItemNo, InternetCategoryID
FROM  
    HLItems
WHERE 
    (InternetCategoryID = 11)

Selecting just the column 

SELECT  
    (CASE 
        WHEN EXISTS (SELECT TOP 1 Caption
                     FROM HLImages
                     WHERE Caption = 'Front Cover') 
           THEN 'Front Cover'
           ELSE 'No Cover' 
     END) AS thumbnail, 
    Title, ItemNo, InternetCategoryID
FROM
    HLItems
WHERE (InternetCategoryID = 11)

Something like this, is what is expected. (ItemNo, and Caption.. shown below for simplicity)

297110 - Front Cover
297110 - NULL
49043128 - Front Cover
49014741 - NULL
50563362 - Front Cover
50301310 - NULL

Upvotes: 1

Views: 539

Answers (2)

sticky bit
sticky bit

Reputation: 37472

Using a RIGHT OUTER JOIN in

SELECT TOP 1 HLImages.Caption 
FROM HLImages  
RIGHT OUTER JOIN HLItems ON HLImages.ItemNo = HLItems.ItemNo

has the effect, that all rows from HLItems (the right table) are included in the result regardless if a matching row from HLImages exists. That means, that this query in an EXISTS is always true. That is why you always got 'Front Cover'from that CASE.

(The EXISTS can only be false if there were no rows in HLItems. But if there weren't, the outer query wouldn't have a non empty result, so you wouldn't see 'No Cover' too in that case.)

And that

SELECT TOP 1 Caption
FROM HLImages
WHERE Caption = 'Front Cover'

will always result in a non empty set if there is any image with the caption 'Front Cover', regardless of the product it belongs to. I assume there are such images, so the same as above applies.

Try a simple correlated subquery using a WHERE.

SELECT    
    (CASE 
        WHEN EXISTS (SELECT *
                     FROM HLImages  
                     WHERE HLImages.ItemNo = HLItems.ItemNo
                           AND HLImages.Caption = 'Front Cover')
           THEN 'Front Cover'
           ELSE 'No Cover' 
     END) AS thumbnail, 
    Title, ItemNo, InternetCategoryID
FROM  
    HLItems
WHERE 
    (InternetCategoryID = 11)

You can remove the AND HLImages.Caption = 'Front Cover' if you only want to see if any image for the product exists, regardless of the caption.

Upvotes: 2

LeviTheOne
LeviTheOne

Reputation: 105

Without knowing the expected result a humble (maybe imperfect) solution would be to use outer apply

SELECT   
case when image.Caption is not null then 'Front Cover'
    else 'No Cover'
end
as thumbnail, 
Title, 
ItemNo, 
InternetCategoryID
FROM  HLItems item
outer apply(
    SELECT   top 1 HLImages.Caption AS Caption
    FROM  HLImages 
    where HLImages.ItemNo = item.ItemNo
) image
WHERE (item.InternetCategoryID = 11)

Upvotes: 0

Related Questions