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! :)

Richard Harrison · Accepted Answer · 2010-09-22 20:18:08Z

7

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..

answered Sep 22, 2010 at 20:18

Richard Harrison

19.5k4 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joe Stefanelli Over a year ago

Amen! "when you start to number fields it is a clue that the database schema needs changing"

Nathan Pitman Over a year ago

Brill, thanks Richard... in this case the client has a number of products I need to indicate trial access for... only 5 in total at the moment but yes I think some abstraction is required here! :)

Wolph · Accepted Answer · 2010-09-22 19:54:57Z

2

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 ...

answered Sep 22, 2010 at 19:54

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

1 Comment

Alec Over a year ago

Assuming the trial value "none" is entered in the database as NULL.

Joe Stefanelli · Accepted Answer · 2010-09-22 20:05:34Z

1

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

answered Sep 22, 2010 at 20:05

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Comments

Dirk · Accepted Answer · 2010-09-22 19:58:48Z

0

This might work:

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

answered Sep 22, 2010 at 19:58

Dirk

31.1k8 gold badges86 silver badges103 bronze badges

Comments

Alec · Accepted Answer · 2010-09-22 19:59:23Z

0

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 ...

answered Sep 22, 2010 at 19:59

Alec

9,1089 gold badges41 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related