Received: from xyzzy.lan.internal (d226-84-103.home.cgocable.net [24.226.84.103]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1AKXZE24083 for ; Sun, 10 Feb 2002 15:33:35 -0500 (EST) (envelope-from drew@xyzzy.dhs.org) Received: from drew by xyzzy.lan.internal with local (Exim 3.33 #1 (Debian)) id 16a0fF-0005p8-00; Sun, 10 Feb 2002 15:33:33 -0500 Date: Sun, 10 Feb 2002 15:33:33 -0500 To: James Carrier Cc: pgsql-sql@postgresql.org Subject: Re: SQL Help - multi values Message-ID: <20020210203333.GA22011@xyzzy.dhs.org> References: <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="NzB8fVQJ5HfG6fxh" Content-Disposition: inline In-Reply-To: <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk> User-Agent: Mutt/1.3.27i From: "Andrew G. Hammond" X-Fnord: +++ath X-WebTV-Stationery: Standard; BGColor=black; TextColor=black X-Message-Flag: Message text blocked: ANTI-MICROSOFT OPINIONS X-BeenThere: drew@xyzzy.dhs.org X-Archive-Number: 200202/103 X-Sequence-Number: 6531 --NzB8fVQJ5HfG6fxh Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Since you've only given us a vague description to work with, I can only= =20 give you a vague answer in return. For situations like what you've described above, I tend to use the=20 following schema: CREATE TABLE widget (=20 widget_id SERIAL PRIMARY KEY, some_data TEXT=20 ); 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 =3D 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=20 -- if that row is selected or not. SELECT * FROM category NATURAL LEFT OUTER JOIN SELECT widget_id FROM w_x_c WHERE widget_id =3D 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=20 AS 'SELECT ($1 || $2 || ''|'')::text;' LANGUAGE 'sql'; CREATE AGGREGATE fugly_concat (=20 BASETYPE =3D text, STYPE =3D text, SFUNC =3D fugly_concat_step, INITCOND =3D '|' ); =20=20=20=20=20=20=20=20=20=20=20=20 SELECT widget_id fugly_concat(category_id::text)=20 FROM w_x_c WHERE widget_id =3D 1 GROUP BY widget_id; Now, when you want to add a new category, simply insert it into the=20 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. --=20 Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~dre= w/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-54= 81 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me --NzB8fVQJ5HfG6fxh Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjxm2R0ACgkQCT73CrRXhLHergCcD2b+JEbe4OKvrZgrX+GXo7Sk EOUAnRfwRK09pFhWY2l0w+xeHttVopCz =W5kF -----END PGP SIGNATURE----- --NzB8fVQJ5HfG6fxh--