You can do:
SELECT last_name, first_name, phone, if(pub_email=Y,email,) as email
FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y
ORDER BY last_name ASC
Gracias, Carlos. This worked fine!
---Fritz
--
MySQL General Mailing List
For list archives:
De-lurking here.
I am trying to figure out how to return results from a query. What I need to do
is to return 4 columns from a database. This is easy:
SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE
`current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC
This
the if(,,)-statement of mysql or by using a union and two selects,
one for pub_email=n and the other for the rest.
Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr
Von: cl c...@nimbleeye.com
An: mysql@lists.mysql.com
Betreff: SELECT subquery problem
De-lurking here.
I am trying to figure out
. Februar 2013 um 15:49 Uhr
Von: cl c...@nimbleeye.com
An: mysql@lists.mysql.com
Betreff: SELECT subquery problem
De-lurking here.
I am trying to figure out how to return results from a query. What I need to do
is to return 4 columns from a database. This is easy:
SELECT last_name, first_name, phone
On Tue, Aug 21, 2012 at 8:26 PM, Larry Martell larry.mart...@gmail.com wrote:
On Tue, Aug 21, 2012 at 8:07 PM, h...@tbbs.net wrote:
2012/08/21 16:35 -0600, Larry Martell
I am trying to write a query that selects from both a correlated
subquery and a table in the main query, and I'm having
from both a correlated
subquery and a table in the main query, and I'm having a lot of
trouble getting the proper row count. I'm sure this is very simple,
and I'm just missing it. I'll try and present a simple example. For
this example, there are 27 rows, organized like this:
mysql select
à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
Date: Tue, 21 Aug 2012 20:26:51 -0600
Subject: Re: help with correlated subquery
From: larry.mart...@gmail.com
To: h...@tbbs.net
CC: mysql@lists.mysql.com
On Tue, Aug 21, 2012 at 8:07 PM, h
...@gmail.com]
Sent: Tuesday, August 21, 2012 7:27 PM
To: h...@tbbs.net
Cc: mysql@lists.mysql.com
Subject: Re: help with correlated subquery
On Tue, Aug 21, 2012 at 8:07 PM, h...@tbbs.net wrote:
2012/08/21 16:35 -0600, Larry Martell
I am trying to write a query that selects from both a correlated
Hello Martin,
On 8/22/2012 8:30 AM, Martin Gainty wrote:
assign realistic alias names
OuterJoin should be called OuterJoin
InnerJoin should be called InnerJoin
Almost! MySQL does not have a simple OUTER JOIN command (some RDBMSes
call this a FULL OUTER JOIN). What we do have is the option
I am trying to write a query that selects from both a correlated
subquery and a table in the main query, and I'm having a lot of
trouble getting the proper row count. I'm sure this is very simple,
and I'm just missing it. I'll try and present a simple example. For
this example, there are 27 rows
[mailto:larry.mart...@gmail.com]
Sent: Tuesday, August 21, 2012 3:35 PM
To: mysql mailing list
Subject: help with correlated subquery
I am trying to write a query that selects from both a correlated
subquery and a table in the main query, and I'm having a lot of trouble
getting the proper row count
)
on data_cst.target_name_id = x.t and ep = x.e
group by target_name_id, ep;
Returns the same result set.
-Original Message-
From: Larry Martell [mailto:larry.mart...@gmail.com]
Sent: Tuesday, August 21, 2012 3:35 PM
To: mysql mailing list
Subject: help with correlated subquery
I am trying
by target_name_id, ep,
wafer_id in the inner query, and then I need to group by
target_name_id, ep in the outer one. I only want to count the number
of target_name_id, ep groups.
Date: Tue, 21 Aug 2012 16:35:23 -0600
Subject: help with correlated subquery
From: larry.mart...@gmail.com
To: mysql
2012/08/21 16:35 -0600, Larry Martell
I am trying to write a query that selects from both a correlated
subquery and a table in the main query, and I'm having a lot of
trouble getting the proper row count. I'm sure this is very simple,
and I'm just missing it. I'll try and present a simple
On Tue, Aug 21, 2012 at 8:07 PM, h...@tbbs.net wrote:
2012/08/21 16:35 -0600, Larry Martell
I am trying to write a query that selects from both a correlated
subquery and a table in the main query, and I'm having a lot of
trouble getting the proper row count. I'm sure this is very simple
: Subquery taking too much time on 5.5.18?
Am 06.07.2012 17:46, schrieb Cabbar Duzayak:
Hi Everyone,
I have been trying to understand why subqueries are taking tooo much
time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release.
In a nutshell, I have 2 tables: A and B. And, I
How fast does this run?
SELECT A.*
FROM A
JOIN B ON B.A_ID = A.id
WHERE B.name LIKE 'X%';
Turning a subquery into a JOIN usually improves performance. (The main
exception is when the subquery consolidates data via GROUP BY, DISTINCT, LIMIT,
etc.)
-Original Message
mysql SELECT @@optimizer_switch;
++
| @@optimizer_switch
|
Hmm,
Looking at the link http://www.artfulsoftware.com/infotree/queries.php
and explanations here, EXISTS() should have performed better, but does
not seem to??? I stopped it after about 5 minutes.
I tried both:
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and
B.name like
On 2012-07-07 9:52 AM, Cabbar Duzayak wrote:
Hmm,
Looking at the link http://www.artfulsoftware.com/infotree/queries.php
and explanations here, EXISTS() should have performed better, but does
not seem to??? I stopped it after about 5 minutes.
I tried both:
SELECT * FROM A WHERE EXISTS
Hi Everyone,
I have been trying to understand why subqueries are taking tooo much
time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release.
In a nutshell, I have 2 tables: A and B. And, I do something like this:
SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like 'X%');
Am 06.07.2012 17:46, schrieb Cabbar Duzayak:
Hi Everyone,
I have been trying to understand why subqueries are taking tooo much
time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release.
In a nutshell, I have 2 tables: A and B. And, I do something like this:
SELECT * FROM A WHERE
As far as I know, a B-tree index can be used by LIKE as long as the string
doesn't begin with a wildcard. LIKE 'X%' should be fine to use an index on
the name column. The index only includes results in the search base which start
with X.
That said, I probably wouldn't use a subquery, either
doesn't begin with a wildcard. LIKE 'X%' should be fine to use an index
on the name column. The index only includes results in the search base which
start with X.
That said, I probably wouldn't use a subquery, either. But since the OP says
they'd prefer to use subqueries, try this and tell
Cabbar, try to replace the IN subquery with an EXISTS. Something like:
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name
like 'X%');
Does it help?
David.
-Original Message-
From: Cabbar Duzayak [mailto:cab...@gmail.com]
Sent: Friday, July 06, 2012 11:46 AM
On 2012-07-06 5:07 PM, David Lerer wrote:
Cabbar, try to replace the IN subquery with an EXISTS. Something like:
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name
like 'X%');
See The unbearable slowness of IN() at
http://www.artfulsoftware.com/infotree
See The unbearable slowness of IN() at
http://www.artfulsoftware.com/infotree/queries.php
Do you read your own links? Excerpt:
In 5.0, EXISTS() is much faster than IN(), but slower than JOIN. In 5.5,
EXISTS() performs about as well as JOIN.
So judging by the subject line...
--
MySQL
figuring out how to use a subquery in
another query. In fact, I'm not even sure if I do need a subquery or
if I can accomplish what I want some other way.
Running:
Server version: 5.1.49-community-log
Protocol version: 10
MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $
I have
I'm having real difficulty figuring out how to use a subquery in another
query. In fact, I'm not even sure if I do need a subquery or if I can
accomplish what I want some other way.
Running:
Server version: 5.1.49-community-log
Protocol version: 10
MySQL client version: mysqlnd 5.0.7-dev
: subquery multiple rows
yea.. almost. but it helped a lot. now i know about those functions
too. thank you...
Nathan Sullivan wrote:
I think you want to do something like this:
select prod, group_concat(category separator ', ')
from products
group by prod;
Hope
|
--
when i do this:
select distinct prod as m, (select category from products where email
= m) as n from products;
i get:
ERROR 1242 (21000): Subquery returns more than 1 row
i know that the subquery returns more than one rows. i hope so...
what i'd like to see as result
|
shoes | fall |
shoes | summer |
--
when i do this:
select distinct prod as m, (select category from products where email
= m) as n from products;
i get:
ERROR 1242 (21000): Subquery returns more than 1 row
i know that the subquery returns more than one
where email
= m) as n from products;
i get:
ERROR 1242 (21000): Subquery returns more than 1 row
i know that the subquery returns more than one rows. i hope so...
what i'd like to see as result is:
-
m | n
|
--
when i do this:
select distinct prod as m, (select category from products where email
= m) as n from products;
i get:
ERROR 1242 (21000): Subquery returns more than 1 row
i know that the subquery returns more than one rows. i hope so...
what i'd like to see as result
On Thu, Feb 25, 2010 at 8:48 AM, Dan Nelson dnel...@allantgroup.com wrote:
IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely
efficient); it's subqueries in general that are killers.
If the dependent subquery is nothing but index lookups, it's still blazingly
fast, though
On Thu, Feb 25, 2010 at 2:48 AM, Dan Nelson dnel...@allantgroup.com wrote:
IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely
efficient);
Yes, I meant to say IN/NOT IN subqueries, not value lists.
it's subqueries in general that are killers.
Subqueries in the FROM clause
that mysqld applies to the subquery, to try to help it
by adding a dependency on the outer query.
There's nothing you can do about this :-( You have to use a JOIN in most cases.
BTW, the general log is itself a performance killer when logged to
tables. If I were you I'd use the slow query log
), whereas I know that the subquery yields
only 50 tuples, so a const join would've made more sense. Any ideas on
how to optimize this by convincing mysql to see the independence use a
const join? (This is in mysql 5.4.3 beta.) Thanks in advance.
mysql explain
select thread_id, argument
a FROM subquery. You should
pretty much always avoid using IN/NOT IN.
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
-and-joins-in-mysql/
You need to rewrite as a join or use a FROM subquery. You should pretty
much always avoid using IN/NOT IN.
IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely
efficient); it's subqueries in general that are killers. Current MySQL
versions almost always
I have the following query:
select concat(weight, ' ', ids, '\n')
from (
select
tableid,
tupleid,
group_concat(id separator ' ') as ids,
(
select count(*)
from (
select transactionid
from transactionlog
where (tableid, tupleid, querytype) =
Hey all...
I am having a problem with sub-queries that I cannot trouble shoot.
I run query a:
select symbol from names where market like 'NYMEX' and name like 'natural
gas {%';
and get 168 names that I manually insert into query b:
select * from endOfDayData where endOfDayData.market like
forever to run;
I've waited at least twenty minutes and not got anything back. I'm running
Ubuntu 9.10 on an intel core i7 with 4GB RAM and 12GB swap... the process
monitor doesn't even flinch so I'm not thinking hardware here... why is the
sub-query running so slow?
MySQL's subquery
... the process
monitor doesn't even flinch so I'm not thinking hardware here... why is the
sub-query running so slow?
Thanks for you help!!
Subqueries are also not indexed. Even if this is an independent
subquery, the optimizer will still need to scan the results of each
subquery for every line
Nick,
select * from endOfDayData where endOfDayData.market like 'NYMEX'
and endOfDayData.symbol IN (select names.symbol from names where
names.market like 'NYMEX' and names.name like 'natural gas {%');
Query c seems to have good syntax as neither the command line mysql
interface nor the gui
Hi All, I am trying to find the top 5 ee_entry id's, and then return
all rows matching any of the 5 ee_entry id's. I think I am close, but
the following sql is currently only returning rows matching the first
ee_entry in the group_concat. If I run the subquery separately and
manually create
as you say you need recursion to build the tree from the table.
if you are interested just in all the employees that are not manager with
their manager info.
select * from emp e1 inner join emp e2 on e1.uidman=e2.uid;
cheers
Claudio
2009/3/10 Ali, Saqib docbook@gmail.com
Hello,
I
Hello,
I have following simplistic DB representing a hierarchy:
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| uid| int(10) | NO | PRI | 0 | |
|
How can I do some recursion to get the UIDs of all the employees
reporting up to a manager, regardless of how deep the tree is. I can
do this usindg LDAP and/or PHP, but not sure how to do it as a mysql
query.
Examples discussion at
Hi
I am able to create an updatable view using a subquery in MySQL 5.1.29
mysql CREATE VIEW v_aa AS
- SELECT *
- FROM flight AS f
- WHERE f.RouteID IN
- (SELECT r.RouteID
- FROM route AS r
- WHERE r.To=
- (SELECT a.AirportID
- FROM
Hi!
On Mon, Feb 9, 2009 at 7:17 AM, blue.trapez...@gmail.com wrote:
Hi
I am able to create an updatable view using a subquery in MySQL 5.1.29
mysql CREATE VIEW v_aa AS
- SELECT *
- FROM flight AS f
- WHERE f.RouteID IN
- (SELECT r.RouteID
- FROM route AS r
of the following:...subquery in the select list.
Your subquery is not in the select list, it is in the where. A
subquery in the select list would be:
CREATE VIEW v_aa AS
SELECT
*
, (SELECT MAX(x) FROM y) AS z
FROM flight
This is not updatable because there is no sensible way to propagate
changes
Your subquery is not in the select list, it is in the where. A
subquery in the select list would be:
CREATE VIEW v_aa AS
SELECT
*
, (SELECT MAX(x) FROM y) AS z
FROM flight
This is not updatable because there is no sensible way to propagate
changes to the y base table.
Great catch
= 'DLR' )
But MySQL returns this error:
#1242 - Subquery returns more than 1 row
I tried also with ANY, IN, EXISTS.
And modified the query into:
SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS t1
WHERE momt = 'MT'. But none works.
What I want to view is, all
/08 10:30 PM, sangprabv [EMAIL PROTECTED] wrote:
Hi,
I tried to look for records from a table with this query:
SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT
binfo FROM sent_sms WHERE momt = 'DLR' )
But MySQL returns this error:
#1242 - Subquery returns more than 1
' )
But MySQL returns this error:
#1242 - Subquery returns more than 1 row
I tried also with ANY, IN, EXISTS.
And modified the query into:
SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR')
AS t1
WHERE momt = 'MT'. But none works.
What I want to view is, all
Hi,
I tried to look for records from a table with this query:
SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT
binfo FROM sent_sms WHERE momt = 'DLR' )
But MySQL returns this error:
#1242 - Subquery returns more than 1 row
I tried also with ANY, IN, EXISTS.
And modified
:
SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT
binfo FROM sent_sms WHERE momt = 'DLR' )
But MySQL returns this error:
#1242 - Subquery returns more than 1 row
I tried also with ANY, IN, EXISTS.
And modified the query into:
SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms
Hi guys,
look at the following test case:
mysql create table temp1( id int)ENGINE=innodb;
Query OK, 0 rows affected (0.18 sec)
mysql create table temp2( tid varchar(10))ENGINE=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql insert into temp1 values(1);
Query OK, 1 row affected
In the last episode (May 14), xian liu said:
mysql select * from temp1;
+--+
| id |
+--+
|1 |
|2 |
|3 |
|4 |
+--+
4 rows in set (0.01 sec)
mysql select * from temp2;
+---+
| tid |
+---+
| 2,3,4 |
+---+
1 row in set (0.00 sec)
Hi guys,
look at the following test case:
mysql create table temp1( id int)ENGINE=innodb;
Query OK, 0 rows affected (0.18 sec)
mysql create table temp2( tid varchar(10))ENGINE=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql insert into temp1 values(1);
Query OK, 1 row affected (0.07
In the last episode (May 15), raid fifa said:
Jerry Schwartz [EMAIL PROTECTED] :
look at the following test case:
mysql create table temp1( id int)ENGINE=innodb;
Query OK, 0 rows affected (0.18 sec)
mysql create table temp2( tid varchar(10))ENGINE=innodb;
Query OK, 0 rows affected
.
To me it seemd logical to do something like this:
SELECT * FROM prices WHERE prices.productid IN (SELECT id FROM priducts
ORDER BY id LIMIT 0, 1000)
However, I'm getting an error-message stating that Limit is not allowed in a
subquery.
How would you approach this?
Thanks,
Geert-Jan
--
View
in a
subquery.
How would you approach this?
I would fetch a list of IDs into the application, then put them into
the IN() list. IN() subqueries will not perform well even if you
didn't have this LIMIT problem.
Actually, I'd probably use mk-archiver and a plugin to do this,
because it uses a non
On Feb 6, 2008 6:40 AM, Britske [EMAIL PROTECTED] wrote:
SELECT * FROM prices WHERE prices.productid IN (SELECT id FROM priducts
ORDER BY id LIMIT 0, 1000)
However, I'm getting an error-message stating that Limit is not allowed in a
subquery.
How would you approach this?
SELECT *
FROM
What I want to accomplish is expressed best as
DELETE FROM prod_price
WHERE prod_price.prod_price_chg_flag = O
AND prod_price.prod_id IN
(SELECT prod_price.prod_id FROM prod_price
WHERE prod_price.prod_price_chg_flag = X)
;
This is clear, concise, and completely
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 7:07 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Forbidden subquery
Hy , i´ve done some search regarding your issue and i think you may
find this interesting :
http://forums.devshed.com/mysql-help-4/got
Hi Jerry!
The very last sentence on:
http://dev.mysql.com/doc/refman/5.0/en/delete.html
is Currently, you cannot delete from a table and select from the same
table in a subquery.
[JS] Yes, I knew that. I just thought that illegal query was the best way of
expressing what I wanted to do
a table and select from the same
table in a subquery.
[JS] Yes, I knew that. I just thought that illegal query was the best way of
expressing what I wanted to do.
But, to bypass that, you can create a temp table and join to that:
[JS] Bingo! It didn't occur to me to make a temporary table
-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 20, 2007 11:25 AM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Forbidden subquery
No problem. I hope by now you figured out I made a typo... :) The
WHERE in the DELETE should
What I want to accomplish is expressed best as
DELETE FROM prod_price
WHERE prod_price.prod_price_chg_flag = O
AND prod_price.prod_id IN
(SELECT prod_price.prod_id FROM prod_price
WHERE prod_price.prod_price_chg_flag = X)
;
This is clear, concise, and completely illegal. I want to
Hy , i´ve done some search regarding your issue and i think you may
find this interesting :
http://forums.devshed.com/mysql-help-4/got-error-134-from-storage-engine-error-number-1030t-446448.html
http://dev.mysql.com/doc/refman/5.0/en/corrupted-myisam-tables.html
2007/12/19, Jerry
Hi Jerry!
The very last sentence on:
http://dev.mysql.com/doc/refman/5.0/en/delete.html
is Currently, you cannot delete from a table and select from the same
table in a subquery.
But, to bypass that, you can create a temp table and join to that:
CREATE TEMPORARY TABLE to_delete
SELECT
You are using a correlated subquery, which MySQL is terrible at.
Whenever you find yourself doing a correlated subquery, see if you
can switch it to a derived table with a join, which MySQL is far
better at. A derived table is like a virtual table you create on
the fly. It's very simple
Indeed, as you say, Brent, correlated subqueries are not well-optimized
in MySQL. The specific subquery (the IN() subquery) demonstrated in the
original post is, however, optimized in MySQL 6.0 :)
More comments inline.
Brent Baisley wrote:
You are using a correlated subquery, which MySQL
Ryan Bates wrote:
I'm trying to determine why a subquery is slower than running two
separate queries. I have a simple many-to-many association using 3
tables: projects, tags and projects_tags. Here's the query I'm using to
find the projects with a given tag:
SELECT * FROM projects WHERE id
Ryan,
Why is it so much faster?
Subquery optimisation in MySQL is a problem. For ideas see 'The
unbearable slowness of IN()' at
http://www.artfulsoftware.com/infotree/queries.php.
PB
Ryan Bates wrote:
I'm trying to determine why a subquery is slower than running two
separate queries. I
I'm trying to determine why a subquery is slower than running two
separate queries. I have a simple many-to-many association using 3
tables: projects, tags and projects_tags. Here's the query I'm using
to find the projects with a given tag:
SELECT * FROM projects WHERE id IN (SELECT
Hi,
I have a query at the moment like this:
SELECT SQL_NO_CACHE STRAIGHT_JOIN t1.col1, t1.col2, t2.col1, ...
MAX(t1.col6)...
(
SELECT Count(DISTINCT col1)
FROM table3 t3
WHERE t3.col1 = t1.col1 AND t3.col2 = t1.col2 AND t3.col1 IN
Hi y'all,
I'ld like to accomplish something like:
set @myvar=concat_ws(,,(SELECT column from table1 order by column;
where ...))
or
select concat_ws(,,(SELECT column from table1 order by column where ...));
for further usage in sql-scripts
--
MySQL General Mailing List
For list archives:
I'ld like to accomplish something like:
set @myvar=concat_ws(,,(SELECT column from table1 order by column;
where ...))
or
select concat_ws(,,(SELECT column from table1 order by column where ...));
for further usage in sql-scripts
I forgot to mention: As I need it in a function, i think i cant
You probably want to look at the group_concat function. It doesn't work
as a subselect, but it allows you to group a set of records and rollup
the different values in the grouping.
Christian Hansel wrote:
I'ld like to accomplish something like:
set @myvar=concat_ws(,,(SELECT column from
, subscriber s
FROM gud
WHERE gud.id != s.GeoClassID
or would this work:
DELETE geodesic_user_data gud, subscriber s
FROM gud
LEFT JOIN subscriber ON geodesic_classifieds_userdata.id=subscriber.GeoClassID
WHERE subscriber.GeoClassID IS NULL;
We're stuck with MySQL 4.0.17 so the subquery approach isn't
Hi,
In your SQL statement you have used subquery. The subquery feature is
introduced in MySQL version 4.1 and later. Thats why you are getting the
Syntax error while running subquery in v4.0.24.
Thanks,
ViSolve DB Team
- Original Message -
From: A Blossom of Paradise [EMAIL
Hello Fellow MySqueelers!,
SELECT version()
reveals...
4.0.24-nt-max-log
I have looked at the following query a hundred times, and cannot find how MYSQL
can report a syntax error to me.
query string is...
SELECT RAND() AS `RAND`, `prod`.`id`
FROM `prod` JOIN `pack` ON `prod`.`id` =
In the last episode (Jan 23), A Blossom of Paradise said:
SELECT version()
reveals...
4.0.24-nt-max-log
I have looked at the following query a hundred times, and cannot find
how MYSQL can report a syntax error to me.
4.0 does not support subqueries. You will need to upgrade to at
step through each query we should come up with something workable.
Get the people:
- Original Message -
From: Brian Dunning [EMAIL PROTECTED]
To: mysql mysql@lists.mysql.com
Sent: Tuesday, January 16, 2007 2:59 PM
Subject: Re: Formatting a subquery?
Hmmm, I see the problem. I can't
where account_id in
(select account_id from accounts order by rand() limit 20)
order by rand()
) as pics group by account_id
- Original Message -
From: Brian Dunning [EMAIL PROTECTED]
To: mysql mysql@lists.mysql.com
Sent: Tuesday, January 16, 2007 2:23 PM
Subject: Formatting a subquery?
I
I can't get MySQL 5 to like this query. Can you tell what I'm trying
to do, and is there a problem with my formatting?
select account_id,picture_id from pictures where account_id in
(select account_id from accounts order by rand() limit 20)
order by rand();
--
MySQL General Mailing List
For
Hmmm, I see the problem. I can't think of any other way to do it. I
have a table of people, and I want to return 20 random people. Each
person has multiple picture records, and I want to return a random
picture for each. That's why I'm trying to put the 'order by rand()'
in the subquery
Of course you realize it's the ORDER BY in the subquery that is giving
you greif. I don't see why you wouldn't get the same net effect if
you remove the ORDER BY from the inner query and leave it on the outer
one.. to my eye, it looks like you would get the same randomized
result.
On 1/16/07
I found that when I use a query as a subquery it will not use the right
index at all??
John.H wrote:
I found that when I use a query as a subquery it will not use the right
index at all??
Post the query, the explain and the relevant table details and someone
might have a suggestion.
Also - not every single query is going to be able to use an index, this
may be one of those
Thanks, again, for folks who suggested solutions to my problem. To help
users searching the archives, I've pasted in a working solution at the
end of this message. Also, I'd like to ask if there is a more efficient
or better way of checking if the language version exist than the six
lines I
I'm trying to use a subquery for the first time, and am having some
problems. I'm hoping someone can give me some suggestions on ways of
doing what I want, and further suggestions if there's a better way of
going about my task.
I have a database of publications in different languages. main
Hi Kevin,
I didn't look that close at it but it
should be IFNULL, not ISNULL which is SQLserver's
version of it.
/Johan
Zembower, Kevin skrev:
I'm trying to use a subquery for the first time, and am having some
problems. I'm hoping someone can give me some suggestions on ways of
doing what I
At 11:40 -0400 26/9/06, Zembower, Kevin wrote:
IF(ISNULL(SELECT lv.langversionid
FROM langversion AS lv
JOIN baseitem AS b3 ON lv.baseitemid =
b3.baseitemid
WHERE lv.langid = 1
AND b.baseitemid =
with subquery
At 11:40 -0400 26/9/06, Zembower, Kevin wrote:
IF(ISNULL(SELECT lv.langversionid
FROM langversion AS lv
JOIN baseitem AS b3 ON lv.baseitemid =
b3.baseitemid
WHERE lv.langid = 1
AND b.baseitemid
I have a query with a subquery which does not throw an error, but does not
return either. I've been testing the query using mysql Query Browser and
the poor dolphin jumps only once a minute or so ;) I use MYSQL's excellent
error documentation heavily so if the query doesn't throw an error
Jeff Drew wrote:
I have a query with a subquery which does not throw an error, but does not
return either. I've been testing the query using mysql Query Browser and
the poor dolphin jumps only once a minute or so ;) I use MYSQL's
excellent error documentation heavily so if the query
1 - 100 of 351 matches
Mail list logo
