Re: SQL Help - multi values

Since you've only given us a vague description to work with, I can only
give you a vague answer in return.

For situations like what you've described above, I tend to use the
following schema:

CREATE TABLE widget (
widget_id SERIAL PRIMARY KEY,
some_data TEXT
);

CREATE TABLE category (
category_id SERIAL PRIMARY KEY,
name TEXT
);

CREATE TABLE w_x_c (
widget_id INTEGER NOT NULL REFERENCES widget,
category_id INTEGER NOT NULL REFERENCES category,
PRIMARY KEY (category_id, widget_id)
);

-- primary key will implicitly create index good for mapping categories
-- to widgets, and here's an index to go the other way
CREATE INDEX w_x_c_rev_idx ON w_x_c (wigdet_id, category_id);

-- Which categories a widget belongs to (naturally you'd use a subselect
-- or whatever instead of 1)
SELECT * FROM category
NATURAL JOIN
SELECT * FROM w_x_c WHERE widget_id = 1;

-- Since you'll be doing this in the context of a web multi select,
-- You probably want a list of all the categories, one per row, with
-- a column (widget_id) that is either NULL or a number to tell you
-- if that row is selected or not.
SELECT * FROM category
NATURAL LEFT OUTER JOIN
SELECT widget_id FROM w_x_c WHERE widget_id = 1;

-- Of course that gives you a row for each category, which is mighty
-- handy if you're doing web work. But it sounds like you'd prefer
-- things in a different format:

CREATE FUNCTION fugly_concat_step (text, text) RETURNS text
AS 'SELECT ($1 || $2 || ''|'')::text;' LANGUAGE 'sql';

CREATE AGGREGATE fugly_concat (
BASETYPE = text, STYPE = text,
SFUNC = fugly_concat_step,
INITCOND = '|'
);

SELECT widget_id fugly_concat(category_id::text)
FROM w_x_c WHERE widget_id = 1
GROUP BY widget_id;

Now, when you want to add a new category, simply insert it into the
category table. Takes a little extra PHP coding, but you won't ever
have to update your code because you've added or changed categories.

--
Andrew G. Hammond mailto:drew(at)xyzzy(dot)dhs(dot)org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me