Postgres Pro
Downloads
Home   >   mailing lists

Thread: Stuck with references

Stuck with references

From
Frodo Larik
Date:
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

Re: Stuck with references

From
"Jim Buttafuoco"
Date:
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@e-l33t.com>
To: pgsql-general@postgresql.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 -------

Re: Stuck with references

From
Michael Fuhr
Date:
On Fri, Mar 25, 2005 at 10:37:31AM -0500, Jim Buttafuoco wrote:
>
> 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 );

This query produces a syntax error due to a missing comma after
lb.name, and "relation does not exist" errors due to the use of "l"
instead of "la" and "lb" in the join conditions.  Try this instead:

SELECT la.name, lb.name, pp.distance
FROM payway_profile AS pp
JOIN location AS la ON (pp.location_a = la.location_id)
JOIN location AS lb ON (pp.location_b = lb.location_id);

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Stuck with references

From
"Jim Buttafuoco"
Date:
I did say untested :)

---------- Original Message -----------
From: Michael Fuhr <mike@fuhr.org>
To: Jim Buttafuoco <jim@contactbda.com>
Cc: Frodo Larik <lists@e-l33t.com>, pgsql-general@postgresql.org
Sent: Fri, 25 Mar 2005 09:05:50 -0700
Subject: Re: [GENERAL] Stuck with references

> On Fri, Mar 25, 2005 at 10:37:31AM -0500, Jim Buttafuoco wrote:
> >
> > 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 );
>
> This query produces a syntax error due to a missing comma after
> lb.name, and "relation does not exist" errors due to the use of "l"
> instead of "la" and "lb" in the join conditions.  Try this instead:
>
> SELECT la.name, lb.name, pp.distance
> FROM payway_profile AS pp
> JOIN location AS la ON (pp.location_a = la.location_id)
> JOIN location AS lb ON (pp.location_b = lb.location_id);
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
------- End of Original Message -------