Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Chris Jewell" <vs0u8055@liv.ac.uk>, <pgsql-sql@postgresql.org>
Subject: Re: Problem with a lookup table!  Please help.
Date: Mon, 9 Dec 2002 13:08:34 -0800
References: <000f01c29fc5$afc34960$2301a8c0@chrisdomain>
In-Reply-To: <000f01c29fc5$afc34960$2301a8c0@chrisdomain>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200212091308.34332.josh@agliodbs.com>


Chris,

> In my capacity as a vet student, I'm trying to create a database of=20
antibiotics.  The way that I have set it up so far is to have one main tabl=
e=20
listing the antibiotics versus their respective efficacies against the four=
=20
major groups of bacteria.  Due to the way that my PHP frontend works, I hav=
e=20
assigned a number to the efficacy - 1 being excellent and 5 being poor=20
efficacy against the particular bacterium.  However, I now want to have a n=
ew=20
table which converts numbers into words.  The problem is this, if I join th=
e=20
main table with the "translation" lookup table, the column names for each o=
f=20
the four categories in the main default to the column name in the lookup=20
table and hence are all the same.  What SQL expression should I use to=20
translate the cryptic numbers into plain english whilst preserving the colu=
mn=20
headings in the main table?

Please post your table definitions as SQL statements.


--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco