How do I return true or false if multiple columns equal a certain value in MySQL query?

Question

I have a single DB table (MySQL) which I am running a simple SELECT on. In this table I have 3 fields which contain 3 possible values each. In each case the values are identical ('none','online','physical'). I want to return in my result a true or false value for an alias if any one of these fields are not set to 'none'.

I can easily evaluate this in PHP after I've returned my result set but in order to sort my results easily I would rather generate the true/false in the SELECT if possible. So at present a result row might look like this:

id: 1 
trial_type_1: none
trial_type_2: online
trial_type_3: none

In this case I want the query to return:

id: 1
trial: True

If all the trial_type fields were set to none then it would return a trial value of False. Any ideas greatly appreciated since I really have no idea where to start or even what to search for online! :)

Answer 1

This should work:

SELECT
    id,
    NOT (trial_type_1 IS NULL AND trial_type_2 IS NULL AND trial_type_3 IS NULL) AS trial
FROM ...

Answer 2

This might work:

SELECT id,
       (trial_type_1 <> 'none' 
        OR trial_type_2 <> 'none' 
        OR trial_type_3 <> 'none')  AS some_not_none
FROM ...

Answer 3

You can use the MySQL IF statement:

SELECT
  id,
  IF (
    (trial_type_1 != 'none'
     OR trial_type_2 != 'none'
     OR trial_type_3 != 'none'
    ), 1, 0
  ) AS trial
FROM ...

Answer 4

You could use CASE:

SELECT ID, CASE WHEN trial_type_1='none' AND trial_type_2='none' AND trial_type_3='none' THEN 'false' ELSE 'true' END
    FROM YourTable

Answer 5

I'd use a case statement within this, it's a very flexible method and can be very useful.

select id, 
(CASE WHEN trial_type_1 <> 'none' OR trial_type_2 <> 'none' 
           OR trial_type_3 <> 'none'
 THEN 'True' ELSE 'False' END) as trial
FROM q3773072

However you could equally well do it as a simple logical operation - which maybe closer to what you want, as follows:

SELECT id, 
       (trial_type_1 <> 'none' OR  trial_type_2 <> 'none'  OR trial_type_3 <> 'none' )  as Trial  
from q3773072

---

The correct way of doing this is of course to store the multiple trial_types in a different table - when you start to number fields it is a clue that the database schema needs changing. You should also set-up another table that defines the trial types and their definition - i.e. whether or not they are a real trial.

I'd probably adopt the latter approach as it is highly likely that you will be adding another trial type at some point in the future and it will be painful if you don't do it right..