Re: Newbie: MYSQL nested query question
Just turn your subquery into another join SELECT C2.City, N.Distance FROM Cities C INNER JOIN Nbc N ON C.CityID = N.PrimaryCityID INNER JOIN Cities C2 ON C2.cityID = N.CityID WHERE C.City = 'Los Angeles' AND N.Distance <20 Shawn Green Database Administrator Unimin Corporation - Spruce Pine Graham Anderson <[EMAIL PROTECTED]> wrote on 04/01/2005 04:49:31 PM: > I upgraded my local mysql version to 4.1.10a and the below query > finally works :) > > How can I now amend the query so it works on my remote server running > mysql 3.23.58 ? From one headache to another ;) > > SELECT ( > > SELECT City > FROM Cities > WHERE CityID = N.CityID > ), N.Distance > FROM Cities C > JOIN Nbc N ON C.CityID = N.PrimaryCityID > WHERE C.City = 'Los Angeles' > AND N.Distance <20 > > many many thanks to all those that replied :) > > g > > On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: > > > Graham Anderson wrote: > >> What is the proper way to say this ? > >> SELECT C.City, N.Distance > >> FROM Cities C > >> JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId > >> WHERE N.CityId = > >> (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') > >> AND N.distance < 20 > >> I am trying to enter in a city and get all the nearby cites with 20 > >> miles > >> Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, > >> and Cities.city > >> learning :) > > Unless I missed something... Why did you make it so hard ? > > SQL is meant to be easy :) > > > > SELECT c.city, n.distance > > FROM Cities c, Nearbycities n > > WHERE c.cityid = n.primarycityid > > AND c.city = 'Los Angeles' > > AND n.distance < 20 > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: Newbie: MYSQL nested query question
strangely, the query works intermittently :( SELECT ( SELECT City FROM Cities WHERE CityId = N.CityId ), N.Distance FROM Cities C JOIN Nbc N ON C.CityId = N.PrimaryCityId WHERE C.City = 'Los Angeles' AND N.Distance <20 sometimes it works...other times it gives the mysql query error: show keys from tbl_properties.php: Missing parameter: table huh ? On Apr 1, 2005, at 2:21 PM, Graham Anderson wrote: In the simple query... the city field showed the result 'Los Angeles' in every row the distance field showed incorrect results to :( City| Distance Los Angeles 18 Los Angeles 5 Los Angeles 7 ... On Apr 1, 2005, at 1:59 PM, Peter Brawley wrote: What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance <20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance < 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance < 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
In the simple query... the city field showed the result 'Los Angeles' in every row the distance field showed incorrect results to :( City| Distance Los Angeles 18 Los Angeles 5 Los Angeles 7 ... On Apr 1, 2005, at 1:59 PM, Peter Brawley wrote: What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance <20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance < 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance < 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance <20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance < 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance < 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance <20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance < 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance < 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance < 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance < 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]