I want to search for a single value in 3 columns and return each occurrence of this value.
My query is based on the following answer regarding a similar request.
SELECT *
FROM table
WHERE 'abc' IN (column1, column2, column3)
However, I don’t want to return the whole row, only the single value. It possible that the value can found in multiple columns for the same row. Each occurrence should be returned and the end result should be a 1-dimensional list. How do I need to alter my query?
>Solution :
The value that you want returned in each row is the same as the value that you search for, so a statement like this will do:
SELECT 'abc' AS column_name
FROM table
WHERE 'abc' IN (column1, column2, column3)
If you want 'abc' returned once for each occurrence in any of the 3 columns you should use UNION ALL:
SELECT column1 AS column_name
FROM table
WHERE column1 = 'abc'
UNION ALL
SELECT column2 AS column_name
FROM table
WHERE column2 = 'abc'
UNION ALL
SELECT column3 AS column_name
FROM table
WHERE column3 = 'abc'