Received: from eba-haus.de (pec-5.au1.f.uunet.de [149.228.241.5]) by hub.org (8.9.3/8.9.3) with SMTP id DAA23383 for ; Wed, 1 Dec 1999 03:39:01 -0500 (EST) (envelope-from DieringG@eba-haus.de) Received: from kopo001 by eba-haus.de;Wed, 01 Dec 1999 08:34:13 GMT with Novell_GroupWise; Wed, 01 Dec 1999 09:34:14 +0100 Message-Id: X-Mailer: Novell GroupWise 5.5 Date: Wed, 01 Dec 1999 09:34:11 +0100 From: "Gerhard Dieringer" To: , Subject: Antw: [SQL] Many booleans Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id DAA23415 >>> Chris Griffin 01.12.1999 04.26 Uhr >>> > I am working on a DB that keeps information on potential job candidates. > One of the pieces of information I need to keep is where they are willing to > relocate. The choices are broken down into 5 regions of the US and northern > and southern California. There are also choices for the continents plus US > and world. If the user puts in a search for the US it needs to match any of > the us regions. If they select any region it needs to match any records with > US or world selected. Currently I have separate boolean fields for each > selection. Is there a better way to do this? Thanks. I'm not sure if I understood your problem, but think you have a hierarchy of regions: total | +-reg1 | | | +-subreg11 | | | +-subreg12 | +-reg2 | +-subreg21 | +-subreg22 If you have a candidate looking for a job in reg1, then a job in subreg11 should match, a job in subreg12 shoold also match, a job in subreg21 shoold not match, .... You have to build a table reglookup candreg | jobreg --------------------------- total | subreg11 total | subreg12 total | subreg21 total | subreg22 reg1 | subreg11 reg1 | subreg12 reg2 | subreg21 reg2 | subreg22 subreg11 | subreg11 subreg12 | subreg12 subreg21 | subreg21 subreg22 | subreg22 Now if you are looking for a job in 'reg1', you write select j.* from jobs j, reglookup r where j.region = r.jobreg and r.candreg = 'reg1'; which gives you all jobs in reg1; If you have many regions, then the table reglookup can get very large and is not easy to maintain. I recently wrote a little C-Programm that builds such table, given a much smaller hierarchy table region | parent ------------------------------ total | reg1 | total reg2 | total subreg11 | reg1 subreg12 | reg1 subreg21 | reg2 subreg22 | reg2 -------------- Gerhard