Received: from bunce.bitecomm.co.uk ([193.128.134.156]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1A6YsE90718 for ; Sun, 10 Feb 2002 01:34:54 -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 2002021006304504:8111 ; Sun, 10 Feb 2002 06:30:45 +0000 Message-Id: <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk> X-Sender: jamesc@bunce.bitecomm.co.uk X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Sun, 10 Feb 2002 06:34:52 +0000 To: pgsql-sql@postgresql.org From: James Carrier Subject: SQL Help - multi values 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 10/02/2002 06:30:45, Serialize by Router on bunce.bitecomm.co.uk/Bite/GB(Release 5.0.8 |June 18, 2001) at 10/02/2002 06:30:46, Serialize complete at 10/02/2002 06:30:46 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200202/99 X-Sequence-Number: 6527 Hello I've just posted this question on the MySQL list but as I'm thinking of moving to PostGresQL I thought I'd post to this list too... Hope someone can help! 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 processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). Any ideas? Please help! Cheers, james