MySQL - selecting where all other columns = 0

Question

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!

Wait, you're altering the table potentially every time the page is loaded? — Interrobang, Dec 28 '11 at 00:54
the alter if field does not exist is a separate statement. the insert is another statement. — ina, Dec 28 '11 at 01:11
ideally, i'd like to be able to use something like "where all others = 0". added more explanatory material, including concrete example. — ina, Dec 28 '11 at 01:22

score 1 · Answer 1 · answered Dec 28 '11 at 01:04

1

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.

answered Dec 28 '11 at 01:04

javidgon

115
8

well, that's exactly what i'm doing: i'm modifying the table using ALTER at runtime – ina Dec 28 '11 at 01:10
so It's easy handle it. Just add a new string every time you modify the table, and then check it out like this: e.g. var string = "c=0"; "SELECT * FROM EXAMPLE WHERE a=1 and b=5 and " + "string" (In case you add the column "c") – javidgon Dec 28 '11 at 01:20
i'm trying to avoid having to explicitly declare the 0 ones... see updated question for the concrete example – ina Dec 28 '11 at 01:38

paxdiablo · Answer 2 · 2011-12-28T01:25:06.550

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.

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 problem with normalizing is that i still have to check for the cases where all others are 0, but then i need an extra join — ina, Dec 28 '11 at 01:12
There's no holistic function in standard SQL nor do I know of any extension for this in any of the vendor-specific implementations I follow. — paxdiablo, Dec 28 '11 at 01:16
i guess i'm not seeing how to normalize it so that the resulting select condition becomes easier. (See updated question above for concrete example) — ina, Dec 28 '11 at 01:36

ypercubeᵀᴹ · Answer 3 · 2011-12-28T02:06:15.287

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

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) — ina, Dec 28 '11 at 01:11

MySQL - selecting where all other columns = 0

3 Answers3