Received: from athena.office.vi.net ([194.88.88.225]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7VEJ2f23082 for ; Fri, 31 Aug 2001 10:19:02 -0400 (EDT) (envelope-from rp@office.vi.net) Received: from rp by athena.office.vi.net with local (Exim 3.12 #1 (Debian)) id 15cp6x-00021W-00; Fri, 31 Aug 2001 15:17:31 +0100 Date: Fri, 31 Aug 2001 15:17:31 +0100 From: Richard Poole To: pgsql-sql@postgresql.org Subject: Re: 2 tables, joins and same name... Message-ID: <20010831151731.F24593@office.vi.net> References: <3B8EA145.1E9349F3@inter-resa.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit User-Agent: Mutt/1.2.5i In-Reply-To: <3B8EA145.1E9349F3@inter-resa.com>; from web@inter-resa.com on Thu, Aug 30, 2001 at 04:25:41PM -0400 X-Archive-Number: 200108/379 X-Sequence-Number: 4638 On Thu, Aug 30, 2001 at 04:25:41PM -0400, Marc André Paquin wrote: > Hello, > > Here is 2 tables: > > airport > --------- > airport_id > name > code > city_id > > destination > ----------- > destination_id > dest_name > ... > airport_dep_id // using airport.airport_id (departure) > airport_arr_id // using airport.airport_id has well (arrival) > > I have 2 columns in the second table that uses the same name column in > the first table... > > I dont know how to formulate my SQL query... I want to select the > destinations in the destination table with not the ID of each airport > but their names. I can do a join with one but with the second one, I get > no results... And this is confusing! > > select dest.dest_name, air.name as airport1, air.name as airport2 from > destination, airport air where dest.airport_dep_id_id=air.airport_id and > dest.airport_arr_id=air.airport_id; You have to join against the airport table twice: SELECT dest.dest_name, air1.name as airport1, air2.name as airport2 FROM desination dest, airport air1, airport 2 WHERE dest.airport_dep_id = air1.airport_id AND dest.airport_arr_id = air2.airport_id; Richard