subject:"Newbie\: MYSQL nested query question"

Re: Newbie: MYSQL nested query question

2005-04-05 Thread SGreen

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

2005-04-01 Thread Graham Anderson

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

2005-04-01 Thread Peter Brawley

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

2005-04-01 Thread Graham Anderson

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

2005-04-01 Thread Graham Anderson

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]

Newbie: MYSQL nested query question

2005-03-31 Thread Graham Anderson

What is the proper way to say this ?
SELECT C.City, N.Distance
FROM Cities C
JOIN Nearbycities N ON C.CityId = 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 :)
--
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

2005-03-31 Thread Philip M. Gollucci

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]

Re: Newbie: MYSQL nested query question

Re: Newbie: MYSQL nested query question

Re: Newbie: MYSQL nested query question

Re: Newbie: MYSQL nested query question

Re: Newbie: MYSQL nested query question

Newbie: MYSQL nested query question

Re: Newbie: MYSQL nested query question

7 matches

Site Navigation

Mail list logo

Footer information