CODEWITHSUNDEEP
sqlsql-server
Laxmi Raghu
Laxmi Raghu

Reputation: 115

sql query to return single value based on column it matches

I've a requirement where in if the query string matches column1 , return me 1. If it matches column 2 return 2 else if it matches column 3 return 3.

Table strunctre:

col1   col2    col3

11      12       13
22      23       24

If my query string is 23, then i'm expecting a return value of 2 as it matches col2.

Something like below:

select 1 
from table1 
where col1=querystring 
      and orderid=xxx 
      or select 2 from table1 
         where col2=querystring 
               and orderid=xxx 
               or select 3 from table1 
                  where col3=querystring  and orderid=xxx

Basically i'm expecting one query which return single value based on the column it matches.

Is it something doable in SQL as i'm not very good in DB skills.

Any help is highly appreciated.

Upvotes: 1

Views: 4242

Answers (3)

StuartLC
StuartLC

Reputation: 107407

There are a couple of approaches. If there is a guarantee that no more than one column will match at a time, a UNION will work:

SELECT 1 AS SomeCol 
FROM   table1 
WHERE  col1 = querystring 
       AND orderid = xxx 
UNION 
SELECT 2 
FROM   table1 
WHERE  col2 = querystring 
       AND orderid = xxx 
UNION 
SELECT 3 
FROM   table1 
WHERE  col3 = querystring 
       AND orderid = xxx;

If more than one match can happen, another approach is this (note the order of precedence is now col1, col2, col3 etc):

SELECT CASE 
         WHEN col1 = querystring THEN 1 
         WHEN col2 = querystring THEN 2 
         WHEN col3 = querystring THEN 3 
       END AS SomeCol 
FROM   table1 
WHERE  orderid = xxx;

Upvotes: 2

BAdmin
BAdmin

Reputation: 937

Try using IF-ELSE condition in your query and check with passed parameter.

Upvotes: 0

TechDo
TechDo

Reputation: 18659

Please try using case

declare @var int
set @var=23

select 
    case @var when col1 then 1
            when col2 then 2
            when col3 then 3 end
from YourTable 
where 
    col1=@var OR 
    col2=@var OR 
    col3=@var

Upvotes: 0

Related Questions