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 <[EMAIL PROTECTED]> 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 ------- ---------------------------(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