Select column containing value from 1 row using find_in_set

Question

I have a table with many rows and columns. I want to find the column containing a certain value, but search only 1 row, when ID=7.

What is the correct way of writing the following expression?

SELECT *   
FROM mytable
WHERE FIND_IN_SET(400, list_column)
WHEN ID=7;

Thank you.

Answer 1

You can't do this in the form you suggest.

The best way to show the column containing 400 in a single query is to use CASE or similar with one case per column under test. For example:

SELECT CASE
   WHEN col1=400 THEN 'col1'
   WHEN col2=400 THEN 'col2'
   WHEN col3=400 THEN 'col3'
   ...
   ELSE 'Not found'
END CASE
FROM mytable
WHERE id=7

It's possible to do it with dynamic SQL (or PHP equivalent) reading all columns from the table using an INFORMATION_SCHEMA query and using those columns in a second query such as the one above.

However, I'm guessing that the reason you want to do this is because your table isn't in first normal form, and that you're storing repeating values in your columns. You might want to look up first normal form to see if that's indeed the case and if you should redesign your database to avoid this and other problems further down the line.