From: | "Jim Buttafuoco" <jim(at)contactbda(dot)com> |
---|---|
To: | Frodo Larik <lists(at)e-l33t(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Stuck with references |
Date: | 2005-03-25 15:37:31 |
Message-ID: | 20050325153552.M37196@contactbda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
try the following (untested) query:
SELECT la.name,lb.name pp.distance FROM payway_profile AS pp
JOIN location AS la ON ( pp.location_a = l.location_id )
join location AS lb ON ( pp.location_b = l.location_id );
---------- Original Message -----------
From: Frodo Larik <lists(at)e-l33t(dot)com>
To: pgsql-general(at)postgresql(dot)org
Sent: Fri, 25 Mar 2005 16:04:17 +0100
Subject: [GENERAL] Stuck with references
> Hello all,
>
> this is my first post to the list, so please be gentle :-)
>
> I created the following tables (full file can be found here:
> http://www.larik.nl/sql/payway.sql ):
>
> CREATE TABLE location (
> location_id integer DEFAULT nextval('"location_id_seq"'::text) NOT NULL,
> name text NOT NULL,
> CONSTRAINT location_pkey PRIMARY KEY (location_id)
> ) WITH OIDS;
>
> CREATE TABLE payway_profile (
> payway_profile_id integer DEFAULT
> nextval('"payway_profile_id_seq"'::text) NOT NULL,
> location_a integer REFERENCES location (location_id) NOT NULL,
> location_b integer REFERENCES location (location_id) NOT NULL,
> distance integer NOT NULL,
> CONSTRAINT payway_profile_pkey PRIMARY KEY (payway_profile_id)
> ) WITH OIDS;
>
> Now I wan to do the following:
>
> A select from payway_profile so I also can see the name of location_a
> and location_b, instead of the id as defined in the table location.
> If there was only reference i could do something like this:
>
> SELECT l.name, pp.distance FROM payway_profile AS pp
> INNER JOIN location AS l ON ( pp.location_a = l.location_id );
>
> But now there are two references location_a and location_b, so I don't
> know how i can solve this in one query. Is it possible or is it bad design?
>
> Sincerely,
>
> Frodo Larik
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
------- End of Original Message -------
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-03-25 16:05:50 | Re: Stuck with references |
Previous Message | Tom Lane | 2005-03-25 15:23:37 | Re: pl/perl problem of memory |