What type of join do I need?

[eMonk]

My old query was:

 

$query = "SELECT city_name FROM city WHERE province_id = 9";

 

Now I'm using:

 

$city = $_GET['city'];

 

and just have the city name now but need to fetch all the cities in the province that $city is in.

 

Here are my tables:

 

Table: city
column: city_name = Vancouver, Victoria, Edmonton
column: province_id = 9, 9, 8

Table: province
column: id = 8, 9
column: name = Calgary, British Columbia

 

So if $city is in province British Columbia, what type of join to I need to fetch all cities listed in British Columbia?

[mikosiko]

self-join

[awjudd]

SELF JOIN

 

SELECT c2.city_name
FROM city AS c
JOIN city AS c2 ON c.province_id = c2.province_id

[fenway]

You'll probably want to add "WHERE c.uid <> c2.uid".