Antw: [SQL] Many booleans
От | Gerhard Dieringer |
---|---|
Тема | Antw: [SQL] Many booleans |
Дата | |
Msg-id | s844eb96.036@kopo001 обсуждение исходный текст |
Список | pgsql-sql |
>>> 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
В списке pgsql-sql по дате отправления: