From: | "Gerhard Dieringer" <DieringG(at)eba-haus(dot)de> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org>, <cgriffin(at)websales(dot)com> |
Subject: | Antw: [SQL] Many booleans |
Date: | 1999-12-01 08:34:11 |
Message-ID: | s844eb96.036@kopo001 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>>> Chris Griffin <cgriffin(at)websales(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick JACQUOT | 1999-12-01 09:32:55 | Re: [SQL] found a way to update a table with data from another one |
Previous Message | Dipankar Chakrabarti | 1999-12-01 05:40:39 | VACUUM PROBLEM |