On Wednesday 19 Feb 2003 4:26 pm, Mark Cubitt wrote:
> I am trying to create a table where one of the fields gets the results from
> another table,
Please try not to reply to an existing message when starting a new question,
it can mess up threading in certain email clients (e.g. mine). Not that I'm
entirely innocent of this myself :-/
> the table I need to get the field from was created like this:
>
> CREATE TABLE "companies" (
> "compid" serial,
> "compname" character varying(100) NOT NULL,
> "compadd1" character varying(100),
> "compadd2" character varying(100),
> "compcity" character varying(150),
> and the new table needs to be like this
>
> CREATE TABLE "location" (
> "compcity" character varying(150),
> "loc_description" character varying(250)
> );
>
> where the "compcity" field is the result of "SELECT DISTINCT(compcity) FROM
> companies;" and update automatically,
Well, there are two ways to crack this nut:
> I think this may be possible with references but I'm not sure if it is or
> how to do it.
1. Yep - Have "compcity" in "companies" reference "location" and have your
application add the required record to "location" if required. Look under
foreign keys in the docs.
2. Use triggers so that updates to "companies" check whether the "location"
table contains the required "compcity" and add it if not. You could even do
similar when entries are deleted from "companies", although that might not be
what you want.
Personally, I'd be tempted by (1) although I'm unclear why. Maybe it's because
this stops idiot users mis-typing "Londoon" or having "New-York" and "New
York".
--
Richard Huxton