[PHP] sql query with join and same column twice...

2001-08-30 Thread Web Manager

Hello,

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

This is not good...

Any help?

Thanks!
--
Marc Andre Paquin

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP] sql query with join and same column twice...

2001-08-30 Thread ERISEN, Mehmet Kamil

Please try
select dest.dest_name, 
   air.name as airport1, 
  air2.name as airport2 
from destination, 
 airport air,
 airport air2
where dest.airport_dep_id_id=air.airport_id and
dest.airport_arr_id=air2.airport_id;

you can use the same table as manu times as you like in
sql. 
It's calld a self join if my memory  does not fail me.
--- Web Manager <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> Here are 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;
> 
> This is not good...
> 
> Any help?
> 
> Thanks!
> --
> Marc Andre Paquin
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, e-mail:
> [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED]
> To contact the list administrators, e-mail:
> [EMAIL PROTECTED]
> 


=
Mehmet Erisen
http://www.erisen.com

__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]