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

On Thu, 30 Aug 2001, Marc [iso-8859-1] 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 probably want to join airport twice because you want two different
airports. Your query would only get flights from one airport
to itself (look at the where condition, you're saying that the row
in airport must have an id that is equal to the departure id *and*
is equal to the arrival id). Probably this:

select dest.dest_name, air1.name as airport1, air2.name as airport2 from
destination, airport air1, airport air2 where dest.airport_dep_id=
air1.airport_id and dest.airport_arr_id=air2.airport_id;