Received: from bunce.bitecomm.co.uk ([193.128.134.156]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1BEHeE52026 for ; Mon, 11 Feb 2002 09:17:40 -0500 (EST) (envelope-from james.carrier@bulletonline.com) Received: from quidditch.bitepr.com ([193.82.143.253]) by bunce.bitecomm.co.uk (Lotus Domino Release 5.0.8) with ESMTP id 2002021114133163:9239 ; Mon, 11 Feb 2002 14:13:31 +0000 Message-Id: <5.1.0.14.2.20020211141623.00ab7900@bunce.bitecomm.co.uk> X-Sender: jamesc@bunce.bitecomm.co.uk X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Mon, 11 Feb 2002 14:17:39 +0000 To: pgsql-sql@postgresql.org From: James Carrier Subject: Re: SQL Help - multi values In-Reply-To: <20020210203333.GA22011@xyzzy.dhs.org> References: <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk> <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk> Mime-Version: 1.0 X-MIMETrack: Itemize by SMTP Server on bunce.bitecomm.co.uk/Bite/GB(Release 5.0.8 |June 18, 2001) at 11/02/2002 14:13:32, Serialize by Router on bunce.bitecomm.co.uk/Bite/GB(Release 5.0.8 |June 18, 2001) at 11/02/2002 14:13:34, Serialize complete at 11/02/2002 14:13:34 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200202/106 X-Sequence-Number: 6534 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