Join

I am new to myssql and am using a mysql database with the following
tables


Table Itinerary
itinerary_id int(4)
ship_id int(4)
itinerary_title varchar(10)
itinerary_duration varchar(10)
date_from date
date_to date
from_port_id int(4)
to_port_id int(4)
port_destinations text
details text

Table Country
country_id int(4)
region_id int(4)
country_name varchar(30)

Table Region
region_id int(4)
region_name varchar(30)


Table Port
port_id int(4)
country_id int(4)
port_name varchar(30)
port_desc text
image_path varchar(50)



in my html form, I choose a field which fetches the data from the
region table. From the region table I select the region_id and then
find the corresponding record(country_id) in the country table. I now
use the country_id to find the port_id from the port table. Once I have
this port_id, I can perform the desired query and fetch the data from
the itinerary table. I am able to do this using three queries but that
takes a lot of time. Can someone help me out?

If someone can also point me to a good tutorial on join, it would be
helpful

Thanks
Pankaj

Re: Join

Pankaj wrote:
> I am new to myssql and am using a mysql database with the following
> tables
>
>
> Table Itinerary
> itinerary_id int(4)
> ship_id int(4)
> itinerary_title varchar(10)
> itinerary_duration varchar(10)
> date_from date
> date_to date
> from_port_id int(4)
> to_port_id int(4)
> port_destinations text
> details text
>
> Table Country
> country_id int(4)
> region_id int(4)
> country_name varchar(30)
>
> Table Region
> region_id int(4)
> region_name varchar(30)
>
>
> Table Port
> port_id int(4)
> country_id int(4)
> port_name varchar(30)
> port_desc text
> image_path varchar(50)
>
>
>
> in my html form, I choose a field which fetches the data from the
> region table. From the region table I select the region_id and then
> find the corresponding record(country_id) in the country table. I now
> use the country_id to find the port_id from the port table. Once I have
> this port_id, I can perform the desired query and fetch the data from
> the itinerary table. I am able to do this using three queries but that
> takes a lot of time. Can someone help me out?
>
> If someone can also point me to a good tutorial on join, it would be
> helpful
>
> Thanks
> Pankaj

SELECT itinerary.* FROM region r
LEFT JOIN country c ON c.region_id = r.region_id
LEFT JOIN port p ON p.country_id = c.country_id
LEFT JOIN itinerary i ON i.from_port_id = p.port_id;