Re: SQL Help - multi values - Mailing list pgsql-sql

Thanks a lot for the help Andrew (and everyone else who responded!)

Cheers

james


At 15:33 10/02/2002 -0500, Andrew G. Hammond wrote:
>   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@xyzzy.dhs.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

James Carrier

Bullet Online :: Aim Higher [http://www.bulletonline.com]
41b Beavor Lane, London W6 9BL

Tel +44 (0) 20 8834 3442
Fax +44 (0) 20 8741 2790