Re: SQL Help - multi values

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Basically the problem I am having is how best to handle multiple
> values for a specific column, in this case the values in
> question are coming from an HTML SELECT MULTI box

Two easy, fairly portable ways that come to mind are to use
boolean columns or to use the bits of a large number. If you have
a very small number of possible values, you might just want to
use boolean columns like this:

CREATE TABLE htmlselect (
title varchar(20),
choice1 bool,
choice2 bool,
choice3 bool
);

This allows you to use SQL like this:

INSERT INTO htmlselect (title,choice1,choice2,choice3)
VALUES ('Pigpen','t','f','t');

SELECT title FROM htmlselect
WHERE choice1 is true OR choice3 is true;

Your application is responsible for keeping the actual
mapping of what each column "means" of course.

A better way (IMO) is to set the choices up as powers of 2, and
use a number to keep track of which values are set:

CREATE TABLE htmlselect (
title varchar(20),
choices integer
);

Your application can add up all the exponents, or you can just
let SQL do it, as below. Use the binary AND operator to test
the values for your WHERE clause.

The SQL looks like this:

INSERT INTO hmtlselect (title, choices)
VALUES ('Pigpen', 2^1 + 2^3 + 2^7);

SELECT title FROM htmlselect
WHERE choices & 2^1 > 0 OR choices & 2^7 > 0;

An integer (in postgreSQL) will let you use 31 different values,
from 2^0 all the way to 2^31. Use smallint if you have 15 values
or less, and bigint will get you up to 62 different values.

Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200202101150

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBPGazL7ybkGcUlkrIEQIiOgCgwDQpNeTL2+7LDmYBrVSniCTPmF4Aniqy
PXL48tR/6anaXXBKZEAdV2n1
=2/dT
-----END PGP SIGNATURE-----