how to find out a result set by which column ? if we write 3 columns in where clause with OR Operators on single table

I have a database table. That table has 4 columns. In 3 columns members(values) want to access the 4th column value.

So here i don’t want to write same query for every member. I want to write only single query. So is it possible with single query? If possible how I can know which column has given those result set?

select 4thcolumn from tablename where lstcolumn=?1 or 2ndcolumn=?2 or 3rdcolumn=?3;

Answer

Using OR is a solution (but that requires you to repeat the parameter three times):

SELECT col4 FROM mytable col1 =:myvalue OR col2 =:myvalue OR col3 = :myvalue;

One solution to shorten the query (and pass a unique parameter) is to use IN:

SELECT col4 FROM mytable WHERE :myvalue IN (col1, col2, col3)

If you want to know which column matched, then this gets longer. In MySQL you can do:

SELECT 
    col4,
    col1 = :myvalue is_col1,
    col2 = :myvalue is_col2,
    col3 = :myvalue is_col3 
FROM mytable 
WHERE :myvalue IN (col1, col2, col3)

This adds three columns in the result set: is_col1, is_col2, is_col3; the column(s) that matched will have value 1, other(s) will show 0.