pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gerhard Dieringer <DieringG@eba-haus.de>
To: pgsql-sql@postgresql.org
To: cgriffin@websales.com
Subject: Antw: [SQL] Many booleans
Date: Wed, 01 Dec 1999 09:34:11 +0100
Message-ID: <s844eb96.036@kopo001> (raw)



>>> Chris Griffin <cgriffin@websales.com> 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






Message-ID: <s844eb96.036@kopo001>
Permalink:  ../s844eb96.036@kopo001/
Also on:    postgresql.org/message-id/s844eb96.036@kopo001

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: DieringG@eba-haus.de, cgriffin@websales.com
  Subject: Re: [SQL] Many booleans
  In-Reply-To: <s844eb96.036@kopo001>
  References: <s844eb96.036@kopo001>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox