Re: 2 tables, joins and same name...

Marc,

> 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!

Whenever you want to join to the same table twice, you need to use your
table aliases to distinguish between instances of the same table. The
way it's written, the query parser cannot distinguish between the two
instances of the airport table ... so it thinks you're asking for all
flights where the departure and arrival airport are the same. Which, of
course, is none.

I'll help with your immediate problem, and then I *highly* suggest you
go out and buy (and read!) Bruce Momjian's book "PostgreSQL:
Introduction and Concepts." (which I believe has been translated if
languages are an issue)

> 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;

SELECT dest.dest_name, depart_air.name as airport1,
arrive_air.name as airport2
FROM desitination dest JOIN airport depart_air
ON dest.airport_dep_id=depart_air.airport_id
JOIN airport arrive_air
ON dest.airport_arr_id=arrive_air.airport_id

Got it?

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco