eMonk Posted December 16, 2011 Share Posted December 16, 2011 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? Quote Link to comment Share on other sites More sharing options...
mikosiko Posted December 16, 2011 Share Posted December 16, 2011 self-join Quote Link to comment Share on other sites More sharing options...
awjudd Posted December 17, 2011 Share Posted December 17, 2011 SELF JOIN SELECT c2.city_name FROM city AS c JOIN city AS c2 ON c.province_id = c2.province_id Quote Link to comment Share on other sites More sharing options...
fenway Posted December 19, 2011 Share Posted December 19, 2011 You'll probably want to add "WHERE c.uid <> c2.uid". Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.