CODEWITHSUNDEEP
mysqlchemistry
ina
ina

Reputation: 19534

MySQL - selecting where all other columns = 0

Suppose you have a table with many property fields, such as:

id
datetime
q
a
c
d
.
.
.
(etc)

where q,a,c,d etc denote an indefinite number of int fields whose names are generated (also created at runtime)

How do you select with the constraint that some specific fields are a specific number and all others are 0?

(for example) a=1, c=5, and all others are 0?

Update:

I guess what i'm asking for is if there is some "holistic" function or something where i can just go "WHERE ALL OTHERS = 0" (or something like that).

The issue with normalizing is that even after the join, I still have to check for both the specific non-zero values as well as whether all other fields (or the normalized field) is 0.

Concrete application:

table of chemical compounds

id
name
C
Fe
Cl
H
.
.
.
(etc - fields are added when new compound insert contains elements not listed)

example row: 

name = Hydrochloric acid
C = 0
Fe = 0
Cl = 1
H = 1
.
.
. 
(everything else = 0)

The problem with having a simple SELECT * FROM table WHERE H=1 AND Cl = 1 is that it also includes the other cases such as C Cl H (which even though it has C=1, it's selected because Cl and H are both 1 as well)

This is why I'm trying to figure out how to select it where all other fields are 0!

Upvotes: 0

Views: 344

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

You construct something like this:

WHERE a=1
  AND c=5
  AND d=0
  AND e=0
  ...

or this:

WHERE (a, c, d, e, ...) =
      (1, 5, 0, 0, ...)

There is nothing like:

WHERE ALL OTHERS = 0

If you are repeatedly changing the table structure, you should dynamically change the query too. But this not a good design. You should normalize the table (probably by splitting the table into 2 tables and changing the repeated columns into one column, so the data goes from many columns to one column but many rows).

Update after the OP's edit

Possible normalization for the chemical compounds - elements:

Compound
--------
CompoundId   Int          Primary Key
Name         Varchar(250) Unique
...other stuff

Element
-------
ElementCode  Char(2)      Primary Key
Name         Varchar(50)  Unique 

Structure
---------
CompoundId   Int 
ElementCode  Char(2) 
Ratio        Int
Primary Key (CompoundId, ElementCode) 
Foreign Key (CompoundId)
  References Compound(CompoundId)
Foreign Key (ElementCode)
  References Element(ElementCode)

with sample data:

CompoundId  Name 
------------------------------
 1          Hydrochloric acid
 2          Water
 3          Glucose

ElementCode  Name 
---------------------
 H           Hydrogen
 C           Carbon
 O           Oxygen 
 Cl          Chlorium 

CompoundId  ElementCode  Ratio 
------------------------------
 1           H            1
 1           Cl           1 
 2           H            2
 2           O            1
 3           H            12
 3           C            6
 3           O            6

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 882136

You can't actually have an indefinite number of columns in a table - you're limited to a definite quantity. In that case, you just use:

... where a = 1 and c = 5 and q = 0 and b = 0 and d = 0 and ...

If you simply meant that the quantity is definite but the number of columns used by your application may change, then the query will need to be generated at runtime as well, using the same information. For example, if d was not used at runtime, it would be left off the query.

But this is a very badly designed table and you should consider normalising it better, such as shifting those attributes to a separate table (one attribute per row in this new table) and linking them back to the original table with the id field, such as:

BaseTable:
    id          integer        primary key
    datetime    timestamp
OtherTable:
    id          integer        references BaseTable(id)
    attribute   char(1)
    text        varchar(50)
    primary key (id,attribute)

This sort of schema will allow arbitrary attributes on each id.

Based on your comments that you're altering the table based on some external factor, I think that's what we in the DBA world would call an "interesting" design decision :-)

I would strongly advise against that but, if you see fit to ignore that advice, you can get at the metadata in most DBA implementations.

For example, MySQL has a COLUMNS table in the INFORMATION_SCHEMA schema taht you can use to enumerate all the columns for a given table. Using that, you could dynamically construct a valid SQL statement based on an arbitrarily complex table.

Not saying that's a good idea, just that it's possible.

Upvotes: 1

javidgon
javidgon

Reputation: 115

It's not a realistic situation. You will never have a indefinite number of int fields because You can't just modify a table without the ALTER statement (only at runtime). At least, you shouldn't.

Upvotes: 1

Related Questions