Geek Answers Handbook

How to return true or false if several columns are equal to a specific value in a MySQL query?

I have one DB table (MySQL) in which I run a simple SELECT. In this table, I have 3 fields, each of which contains 3 possible values. In each case, the meanings are identical ("no", "online", "physical"). I want to return a true or false value for the alias in my result if any of these fields is not set to "none".

I can easily evaluate this in PHP after I have returned my result set, but in order to easily sort the results, I would rather generate true / false in SELECT, if possible. So, now the result line might look like this:

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

In this case, I want the request to be returned:

 id: 1 trial: True

If all trial_type fields are set to none, it will return a trial value of False. Any ideas are greatly appreciated since I really don't know where to start or even search the Internet! :)

+4
source share
5 answers

I would use the case statement in this case, it is 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 this as a simple logical operation, which is perhaps 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 to do this, of course, is to store several test_tests in another table - when starting the number of fields, this is the key to the need to change the database schema . You should also set up another table that defines the types of samples and their definition - that is, regardless of whether it is a real trial version.

I would probably take the latter approach, as it is very likely that in the future one more type of sample will be added to you and it will hurt if you do not do it right.

+6
source

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 ...
+2
source

You can 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
+1
source

This might work:

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

You can use 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 ...
0
source

More articles:


All Articles