Mysql – thesql select query to select from single table with multiple WHERE clauses

MySQL

i am new to database querying so apologies for this rudimentary query question. thank you in advance.

table called "neuron"  
columns in "neuron" called "neuron_id", "neuron_name" + others (not relevant here)

neuron_id is unique identifier for each neuron_name;
say there are 5 entries, thus
neuron_id = 1,2,3,4,5 and corresponding
neuron_name = a,b,c,d,e

i would like to generate a list that contains neuron_id for select neuron_name. the following query does not work for me.

select neuron_id, neuron_name  
from neuron  
where neuron_name = "a" or "c" or "e"

i expect the following

1,a  
3,c  
5,e

but the above query only spits out

1,a

Best Answer

Try this:

select neuron_id, neuron_name  
from neuron  
where neuron_name = "a" or neuron_name = "c" or neuron_name = "e"

Edit:

The solution from ypercube is more semantically correct.

select neuron_id, neuron_name  
from neuron  
where neuron_name IN ('a', 'c', 'e')

Related Solutions

Mysql – How to improve this nested MySQL query to avoid redundant WHERE statement

Reformulating your query:

SELECT 
    numopens / numsends AS Open_Rate,
    numactions / numsends AS Action_Rate
FROM
    ( SELECT 
          ( SELECT COUNT(DISTINCT s.userID) 
            FROM tbl_sends AS s
            WHERE s.mailing_id = param.mailing_id
          )  
        AS numsends,
          ( SELECT COUNT(DISTINCT o.userID) 
            FROM tbl_opens AS o
            WHERE o.mailing_id = param.mailing_id
          )  
        AS numopens
          ( SELECT COUNT(DISTINCT a.userID) 
            FROM tbl_actions AS a 
            WHERE a.mailing_id = param.mailing_id
          )
        AS numactions
      FROM 
          ( SELECT 
                5694     AS mailing_id,
                417      AS another_parameter,
                'funny'  AS one_more_parameter   
          ) AS param  
    ) AS cte ;

MySQL – How to Create Identity Field Without Auto-Increment

You can have almost any field as the primary key and index it as such. However it would be down to the logic further up the stack (your scripting language most probably to provide unique IDs. You will almost definitely find yourself making additional queries to find unused IDs as the DB grows.

One approach to alpha numeric to numeric conversion would be a key table int(11) Auto in one col and VarChar(18) in the other. Thus you would actually be using common or garden numeric keys on the tables but would have a user handle them in the keys table. You would also have unique IDs across all tables and thus the user key would also implicate table as well. Simply setting the user key as indexed, unique would mean that you would try to write the key and if the row insert was a success then you are good to use the ID (and/or the key depending on what you are doing) but on a fail you would need a new key and could bounce back to the user for a new reference.

However have you considered using numeric keys in the data logic layer but showing user labels in the UI layer thus users can call their object Bob, Harry or Alpha123456FooBar1 without strange plumbing lower down to accommodate them.

In your case you want to show the user a concat key which at the data layer needs to be stored as two separate keys. This concat key seems from what you have said to define a singular one-to-one relationship which is not really it's job. It sounds like what you want is a key to the relationship which brings us back to a lookup table. here you would have the objects table use numeric keys (from the object and other object tables) and provide a unique text value (MD5/SHA1/etc (of the key concat) might work 99.9% of the time) as the label which could be enforced with index, unique.

You could also limit the int size of the object key to four digits (9999 possible entries) in value and (I guess) use VarChar keys in the other table (if you must). Thus if the user requests 0001abceFGhJk123 you can split that into 0001 (1) and abceFGhJk123.

This is far from ideal as numeric auto provided keys are so much easier to deal with. Then again you could create a lookup table of separate keys and concat-key/label (as per the universal ID I mentioned before) if that is really important to you.

One workaround (if you are determined that the users must be able to have alpha numeric keys but you know that numeric keys are easier at the data layer) is to use numeric keys at the data layer and convert the numeric base to show letters to the user. For example shifting to base16 would give you 0-9 and a-f (case insensitive) and this sort of conversion is built in to most languages, or base36 would give you 0-9 and a-z (case insensitive) while base62 would give you 0-9 a-z and A-Z although to be honest you would need a significantly huge data set to make a 14 character data stub that was not mostly padded out with zeros. MySQL lets you override the auto value with a number of your own (provided it does not yet exist) and if these key values are coming from users shifting down from a base to base10 might work.

Whatever approach you use you will be very grateful, down the line, that you kept it simple at the database layer and kept your keys separate for actual primary key data.

Best Answer

Related Solutions

Mysql – How to improve this nested MySQL query to avoid redundant WHERE statement

MySQL – How to Create Identity Field Without Auto-Increment

Related Question