SQL query to select alternate value if a value is not found
I am trying to write a sql query such as that in where condition of my query if I search for a key that does not exist, it should take value of key null in below example.
i.e. if I use below query
select value
from myTable
where Id=1 and Key = 't'
As key 't' does not exist, it should take value on key null. So I want to write my query something like this.
select value
from myTable
where Id=1
and (if key = 't' exist then give its value else give value of key = null)
| Id | Key | value |
|---|---|---|
| 1 | null | val1 |
| 1 | a | val2 |
| 2 | x | val3 |
| 2 | y | val4 |
| 2 | null | val5 |
| 3 | p | val6 |
| 4 | q | val7 |
It is not possible for there to be two rows for a given Id to have a key that is NULL, and there will always be at least one row for any Id that is either t or NULL.
One way would be to apply row numbers in order of preference:
DECLARE @id int = 1;
;WITH x AS
(
SELECT Id, [Key], [value], en = ROW_NUMBER() OVER
(ORDER BY CASE WHEN [Key] = 't' THEN 1
WHEN [Key] IS NULL THEN 2 END)
FROM dbo.table
WHERE Id = @id
AND ([Key] = 't' OR [Key] IS NULL)
)
SELECT Id, [Key], [value] FROM x WHERE rn = 1;
That allows you to apply additional rules, like what to do if there are multiple rows with NULL or no rows with either.
A simpler route if those aren't possibilities:
SELECT TOP (1) Id, [Key], [value]
FROM dbo.table
WHERE Id = @id
AND ([Key] = 't' OR [Key] IS NULL)
ORDER BY [Key] DESC; -- puts NULLs last