AT-2016 AT-2016 - 2 months ago 9
SQL Question

Get Product Image If The Status is Equal To 1

This looks simple but got stuck. So let me share the table structure first:

Products:

CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NULL,
[CategoryID] [int] NULL,
[ProductName] [nvarchar](100) NULL,
[Details] [nvarchar](max) NULL,
[Price] [float] NULL,
[Stock] [float] NULL
)


ProductImages:

CREATE TABLE [dbo].[ProductImages](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NULL,
[ImageName] [nvarchar](100) NULL,
[ImageDetails] [nvarchar](max) NULL,
[Status] [int] NULL
)


The ProductID is the foreign key in the ProductImages table. So what I want is to show all the products with images whose Status = 1 (Status column in the ProductImages table). Now here is a logic. If I do INNER or LEFT JOIN both the tables, then it'll show the products that has status 1 with following query:

SELECT m.ProductID, m.CategoryID, m.ProductName, m.Details, m.Price,
m.Stock, k.ImageName, k.Status FROM Products m LEFT JOIN ProductImages k
ON k.ProductID = m.ProductID WHERE k.Status = 1


But my requirement is all the products will be shown with images by default like (No images available). Only the images from ProductImages table will be shown up when it's Status is 1. If Status = 0, then the product is shown but with no images text or whatever. Here is a sample output that I supposed to get:

image_status

Note: Only the images with status 1 will be shown else by default no images with product details.

Answer

I'm a bit confused with your status column from both outputs, but if I understand you correctly this is a simple CASE expression showing "No images" string when Status <> 1 (meaning the left join didn't find any images with Status = 1) else show the value from that column:

SELECT 
  m.ProductID, m.CategoryID, m.ProductName, m.Details, m.Price, m.Stock, 
  k.Status,
  CASE WHEN k.Status <> 1 THEN 'No images' ELSE k.ImageName END AS ImageName, 
FROM Products m 
LEFT JOIN ProductImages k
  ON k.ProductID = m.ProductID AND k.Status = 1
Comments