CODEWITHSUNDEEP
sqlsql-server
Lucy
Lucy

Reputation: 253

SQL Server How to get NULL when its not match

I want to get NULL when it's not match with the query ....

this is how my table look like

enter image description here

enter image description here

And here is my t-sql

SELECT Product.ProductId,
       Product.ProductName,
       Product.ProductPrice,
       Product.ProductQuantity,
       property.PropertyId,
       ProductUnderCategory.SubCategoryId,
       SubCategory.SubCategoryName,
       EE.propertyname AS eee,
       ProductProperties.PropertyValue
FROM Product
JOIN ProductProperties ON Product.ProductId = ProductProperties.ProductId
JOIN property ON property.PropertyId = ProductProperties.PropertyId,
     property AS EE
JOIN CategoryProperty ON EE.PropertyId = CategoryProperty.PropertyId,
     SubCategory
JOIN ProductUnderCategory ON SubCategory.SubCategoryId = ProductUnderCategory.SubCategoryId
WHERE Product.ProductId = 10
    AND ProductUnderCategory.ProductId = 10
    AND CategoryProperty.SubCategoryId = ProductUnderCategory.SubCategoryId

Upvotes: 1

Views: 49

Answers (1)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You could use CASE expression to check If EE.propertyname = 'Contents' then use NULL as value, otherwise use ProperyValue in following:

SELECT Product.ProductId,
       Product.ProductName,
       Product.ProductPrice,
       Product.ProductQuantity,
       property.PropertyId,
       ProductUnderCategory.SubCategoryId,
       SubCategory.SubCategoryName,
       EE.propertyname AS eee,
       CASE WHEN EE.propertyname = 'Contents' THEN NULL 
            ELSE ProductProperties.PropertyValue 
       END as PropertyValue 
FROM Product
JOIN ProductProperties ON Product.ProductId = ProductProperties.ProductId
JOIN property ON property.PropertyId = ProductProperties.PropertyId,
     property AS EE
JOIN CategoryProperty ON EE.PropertyId = CategoryProperty.PropertyId,
     SubCategory
JOIN ProductUnderCategory ON SubCategory.SubCategoryId = ProductUnderCategory.SubCategoryId
WHERE Product.ProductId = 10
    AND ProductUnderCategory.ProductId = 10
    AND CategoryProperty.SubCategoryId = ProductUnderCategory.SubCategoryId

Upvotes: 1

Related Questions