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
G
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
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 Brawle
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 Cit
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
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 a
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
matt_lists <[EMAIL PROTECTED]> wrote on 02/02/2005 08:50:16 AM:
> Jay Blanchard wrote:
>
> >[snip]
> >
> >
> >>>No, it isn't ignored...it just returns a FALSE for the IN statement
> >>>
> >>>
> >[/snip]
> >
> >More info
> >
> >"The word IN is an alias for = ANY. Thus these two statements a
Jay Blanchard wrote:
[snip]
No, it isn't ignored...it just returns a FALSE for the IN statement
[/snip]
More info
"The word IN is an alias for = ANY. Thus these two statements are the
same:
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN(SELE
[snip]
>>
>> No, it isn't ignored...it just returns a FALSE for the IN statement
[/snip]
More info
"The word IN is an alias for = ANY. Thus these two statements are the
same:
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2);
Howeve
[snip]
Not only that, shoudlnt it say "column does not exist?" It does not
return an error, it ignores the whole nested query
[/snip]
Not really, because the entire sub query is being viewed as an OR
condition (because of using IN).
--
MySQL General Mailing List
For list arch
matt_lists wrote:
Jay Blanchard wrote:
[snip]
the bug is, the nested query on the first statement is ignored
[/snip]
No, it isn't ignored...it just returns a FALSE for the IN statement
False should give no records, it's an "IN ()" sense none match the
condition
inste
Jay Blanchard wrote:
[snip]
the bug is, the nested query on the first statement is ignored
[/snip]
No, it isn't ignored...it just returns a FALSE for the IN statement
False should give no records, it's an "IN ()" sense none match the condition
instead it gives me eve
[snip]
the bug is, the nested query on the first statement is ignored
[/snip]
No, it isn't ignored...it just returns a FALSE for the IN statement
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
x27;,'E','F','G','H','I','J'))
There's no "RIC_STOR" in the DODAAF table, the column is called "RIC"
changing the sql to this works fine
SELECT 'SBC' FIN_RIC, BRAGG_ISS.* FROM BRAGG_ISS WHERE ARCHIVE_DAT
[snip]
SELECT A.*, b.*
FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE c1
= 'c' AND c2= 'c' );
[/snip]
This query is just badly formed, for instance, where is the table
aliased 'b' in your from statement? Are you showing the complete query?
--
MySQL General Mailing List
Fo
orrectly, as far as I can tell,
still working on validating data
SELECT A.*, b.*
FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE
c1 = 'c' AND c2= 'c' );
the first sql shouldnt even run, when you run the nested query alone,
ie SELECT BLAH FROM tableC WH
I can tell, still
working on validating data
SELECT A.*, b.*
FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE c1
= 'c' AND c2= 'c' );
the first sql shouldnt even run, when you run the nested query alone, ie
SELECT BLAH FROM tableC WHERE c1 = &
meets the condition in the inner
>select. So, for each and every value in the table main, it has to
>re-computing the inner query and scan the results for matches.
Not sure why this would happen. The nested query is not correlated to
the outer query, so I would expect it to be executed
(large) returns 496 rows in 0.17 seconds.
>
> Have tried playing around with some of the system variables:
> query_cache_size, innodb_buffer_pool_size with no real affect.
>
> In our production environment (record size is much larger, similar
> number of records to the large test
ws in 0.16 seconds.
(large) returns 496 rows in 0.17 seconds.
Have tried playing around with some of the system variables:
query_cache_size, innodb_buffer_pool_size with no real affect.
In our production environment (record size is much larger, similar
number of records to the large test set), both th
Subselects are only available from 4.1 - that's why you have an error.
Terry
--Original Message-
> Hi all
> I have Mysql 3.23.45 on linux.
>
> while trying this command :
> SELECT outbox_id
> FROM outbox
> WHERE send_time=(SELECT MAX(send_time) FROM outbox WHERE
> subs_id=
Hi all
I have Mysql 3.23.45 on linux.
while trying this command :
SELECT outbox_id
FROM outbox
WHERE send_time=(SELECT MAX(send_time) FROM outbox WHERE subs_id="myid");
I encounter this error:
ERROR 1064: You have an error in your SQL syntax near 'SELECT MAX(send_time) FROM
outbox WHERE subs
"Bruno Batarelo" <[EMAIL PROTECTED]> wrote:
> I have a problem to report. There is a SELECT query that selects data from
> three tables according to certain criteria. It goes like this:
>
> SELECT bglavna.T001, bpolja.ID AS ID2, bpotpolja.TEKST
> FROM (bglavna INNER JOIN bpolja ON bglavna.ID=bpolj
Hello all
I have a problem to report. There is a SELECT query that selects data from
three tables according to certain criteria. It goes like this:
SELECT bglavna.T001, bpolja.ID AS ID2, bpotpolja.TEKST
FROM (bglavna INNER JOIN bpolja ON bglavna.ID=bpolja.FK) INNER JOIN
bpotpolja ON bpolja.ID=bpo
make that a string,
> then do another query with member in ($string). Is there a way to do it
> with a nested query? Something like:
>
> SELECT username FROM members WHERE referer IN (SELECT id FROM members
> WHERE referer = 5);
Sub-selects are not supported in mysql, but y
query with member in ($string). Is there a way to do it
with a nested query? Something like:
SELECT username FROM members WHERE referer IN (SELECT id FROM members
WHERE referer = 5);
TIA!
-
Before posting, please check:
http
query with member in ($string). Is there a way to do it
with a nested query? Something like:
SELECT username FROM members WHERE referer IN (SELECT id FROM members
WHERE referer = 5);
TIA!
-
Before posting, please check:
http
Hi,
I just realized that mysql does not have nested queries, and
I need to do something like this:
update table1 set A="blah" where B=(select B from table1, table2 where
table1.B=table2.B)
Is it possible?
Thanks,
Jeff Pavel [EMAIL PROTECTED]
---
29 matches
Mail list logo
