CODEWITHSUNDEEP
sqlsql-server
user11566010
user11566010

Reputation: 33

Return null if value does not exist

I have a table with project data, and I want to return NULL or "Not_Exist" as a result if the project_ID was not found.

For example:

SELECT a.[Project ID], a.[Stage]
FROM Projects a
WHERE a.[Project ID]  IN ('CR324S', 'a')

CR324S exists in the table but 'a' is not there, so the results that I want would be:

Project ID     Stage
-----------------------------
CR324S         Implementation
a              Not_Exist

Upvotes: 3

Views: 3888

Answers (1)

Fahmi
Fahmi

Reputation: 37483

You can try below -

  1. first construct a set of value using union
  2. then use left join and put your condition in ON Clause
SELECT a.[pid], coalesce(b.[Stage],'Not_Exist') as stage
from
(
    select 'CR324S' as pid 
    union 
    select 'a'
)A left join Projects B on A.pid=B.[Project ID] and [Project ID]  IN ('CR324S','a')

OR 

SELECT a.[pid], coalesce(b.[Stage],'Not_Exist') AS stage
FROM   (VALUES ('CR324S'), ('a')) A(pid)
LEFT JOIN Projects B on A.pid=B.[Project ID] AND [Project ID]  IN ('CR324S','a')

Upvotes: 2

Related Questions