CODEWITHSUNDEEP
mysqlsqlone-to-many
Bob Tway
Bob Tway

Reputation: 9613

Using SQL to select records with a single "true" bit field from several bit fields

If I have a table like this:

CREATE TABLE `Suppression` (
  `SuppressionId` int(11) NOT NULL AUTO_INCREMENT,
  `Address` varchar(255) DEFAULT NULL,
  `BooleanOne` bit(1) NOT NULL DEFAULT '0',
  `BooleanTwo` bit(1) NOT NULL DEFAULT '0',
  `BooleanThree` bit(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`SuppressionId`),
)

Is there a set-based way in which I can select all records which have exactly one of the three bit fields = 1 without writing out the field names?

For example given:

1 10 Pretend Street 1 1 1 
2 11 Pretend Street 0 0 0 
3 12 Pretend Street 1 1 0 
4 13 Pretend Street 0 1 0 
5 14 Pretend Street 1 0 1 
6 14 Pretend Street 1 0 0

I want to return records 4 and 6.

Upvotes: 1

Views: 1985

Answers (3)

O. Jones
O. Jones

Reputation: 108776

If you

  1. must use this denormalized way of representing these flags, and you
  2. must be able to add new flag columns to your table in production, and you
  3. cannot rewrite your queries by hand when you add columns,

then you must figure out how to write a program to write your queries.

You can use this query to retrieve a result set of boolean-valued columns, then you can use that result set in a program to write a query involving all those columns.

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'Suppression'
   AND COLUMN_NAME LIKE 'Boolean%'
   AND DATA_TYPE = 'bit'
   AND NUMERIC_PRECISION=1

The approach you have proposed here will work exponentially more poorly as you add columns, unfortunately. Any time a software engineer says "exponential" it's time to run away screaming. Seriously.

A much more scalable approach is to build a one-to-many relationship between your Suppression rows and your flags. Add this table.

CREATE TABLE SuppressionFlags (
   SuppressionId int(11) NOT NULL,
   FlagName varchar(31) NOT NULL,
   Value bit(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (SuppressionID, FlagName)
)

Then, when you want to insert a row with some flag variables, do this sequence of queries.

 INSERT INTO Suppression (Address) VALUES ('some address');
 SET @SuppressionId := LAST_INSERT_ID();
 INSERT INTO SuppressionFlags (SuppressionId,   FlagName,    Value)
                       VALUES (@SuppressionId, 'BooleanOne',   1);
 INSERT INTO SuppressionFlags (SuppressionId,   FlagName,    Value)
                       VALUES (@SuppressionId, 'BooleanTwo',   0);
 INSERT INTO SuppressionFlags (SuppressionId,   FlagName,    Value)
                       VALUES (@SuppressionId, 'BooleanThree', 0);

This gives you one Suppression row with three flags set in the SuppressionFlags table. Note the use of @SuppressionId to set the Id values in the second table.

Then to find all rows with just one flag set, do this.

 SELECT Suppression.SuppressionId, Suppression.Address
   FROM Suppression
   JOIN SuppressionFlags ON Suppression.SuppressionId = SuppressionFlags.SuppressionId
  GROUP BY Suppression.SuppressionId, Suppression.Address
 HAVING SUM(SuppressionFlags.Value) = 1

It gets a little trickier if you want more elaborate combinations. For example, if you want all rows with BooleanOne and either BooleanTwo or BooleanThree set, you need to do something like this.

SELECT S.SuppressionId, S.Address
  FROM Suppression S
  JOIN SuppressionFlags A ON S.SuppressionId=A.SuppressionId AND A.FlagName='BooleanOne'
  JOIN SuppressionFlags B ON S.SuppressionId=B.SuppressionId AND B.FlagName='BooleanTwo'
  JOIN SuppressionFlags C ON S.SuppressionId=C.SuppressionId AND C.FlagName='BooleanThree'
 WHERE A.Value = 1 AND (B.Value = 1 OR C.Value = 1)

This common database pattern is called the attribute / value pattern. Because SQL doesn't easily let you use variables for column names (it doesn't really have reflection) this kind of way of naming your attributes is your best path to extensibility.

It's a little more SQL. But you can add as many new flags as you need, in production, without rewriting queries or getting a combinatorial explosion of flag-matching. And SQL is built to handle this kind of query.

Upvotes: 1

RaphaMex
RaphaMex

Reputation: 2839

If your number of booleans can vary over time and you don't want to update your code, I suggest you make them lines and not columns. For example: 

CREATE TABLE `Suppression` (
  `SuppressionId` int(11) NOT NULL AUTO_INCREMENT,
  `Address` varchar(255) DEFAULT NULL,
  `BooleanId` int(11) NOT NULL,
  `BooleanValue` bit(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`SuppressionId`,`BooleanId`),
)

So with 1 query and a 'group by' you can check all values of your booleans, however numerous they are. Of course, this makes your tables bigger.

EDIT: Just came out with another idea: why don't you have a checksum column added, whose value would be the sum of all your bits? So you would update it at every write into your table, and just check this one in your select

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You could "add them up":

where cast(booleanone as unsigned) + cast(booleantwo as unsigned) + cast(booleanthree as unsigned) = 1

Or, use tuples:

where ( (booleanone, booleantwo, booleanthree) ) in ( (0b1, 0b0, 0b0), (0b0, 0b1, 0b0), (0b0, 0b0, 0b1) )

I'm not sure what you mean by "set-based".

Upvotes: 3

Related Questions