Clever Geek Handbook

Mysql: look up value in two columns and return the colum name he liked

for example we have the following table with columns

id a b
 1 1 2 
 2 3 4 
 3 5 6 
 4 7 8

and we are looking for the value 7 in column a and column b is there a way to query the table and return the column name 'a' ????

+3


source to share


1 answer


First select all rows where a or b is 7. Then use a case statement to return the column name, which is 7.

select id,
    case when a = 7
    then 'a'
    else 'b'
    end
from mytable
where 7 in (a,b)

Another way to use union all 



select id , 'a'
from mytable where a = 7
union all
select id , 'b'
from mytable where b = 7

If both a and b can be 7 for the same row, the query union all

will return row IDs where both values ​​are 7 times for each column, whereas the first query only returns them once with a value a

.

+1


source






More articles:


All Articles