In the last episode (Dec 20), Johnny Withers said:
> On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair wrote:
> > I have table post (id INT and parent VARCHAR)
> >
> > +--+-+
> > | id | parent |
> > +--+-+
> > |1 | 0 |
> > |2 | 0 |
The sub-select only returns a single row, so IN(...) is only looking at a
single value in the list .. it doesn't "expand" to into IN (5,7,11).
On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair wrote:
> I have table post (id INT and parent VARCHAR)
>
> +--+-+
> | id | parent
I have table post (id INT and parent VARCHAR)
+--+-+
| id | parent |
+--+-+
|1 | 0 |
|2 | 0 |
|3 | 1 |
|4 | 0 |
|5 | 1 |
|6 | 0 |
|7 | 1,5 |
|8 | 1,5 |
>-Original Message-
>From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
>Sent: Tuesday, September 21, 2010 11:48 AM
>To: Johan De Meersman
>Cc: [MySQL]
>Subject: Re: SELECT WHERE IN help
>
>Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data
Hi Neil, all!
Tompkins Neil wrote:
> Hi
>
> With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
> return two records for the record_id 3 ? Is it possible ?
This is a case where you may safely use natural language and logic. The
command is
SELECT all fields FROM the rec
On 21/09/2010 16:44, Tompkins Neil wrote:
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table. However, because 3 exists twice
within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is
it possible ?
No, that isn't
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a
table. I think in this case I need to change my design .
On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman wrote:
> I don't think that'll work, no. Why would you want to return duplicate data
> ? The whole point of an R
I don't think that'll work, no. Why would you want to return duplicate data
? The whole point of an RDBMS is to *avoid* duplicate data :-)
On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil wrote:
> Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
> only exists once in the
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table. However, because 3 exists twice
within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is
it possible ?
Cheers
Neil
On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meers
If there are two, you will return two.
On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil wrote:
> Hi
>
> With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
> return two records for the record_id 3 ? Is it possible ?
>
> Cheers
> Neil
>
--
Bier met grenadyn
Is als mosterd
Hi
With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
return two records for the record_id 3 ? Is it possible ?
Cheers
Neil
Mudd wrote:
> >> > So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I
> >> > propose valid and SHOULD the optimiser recognise this and be expected
> >> > to just find the 2 rows by searching on the primary key?
> >>
> >>
Simon,
On Wed, May 27, 2009 at 11:23 AM, Simon J Mudd wrote:
> per...@elem.com (Perrin Harkins) writes:
>
>> On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd wrote:
>> > So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I
>> > propose valid and SHOUL
per...@elem.com (Perrin Harkins) writes:
> On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd wrote:
> > So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I
> > propose valid and SHOULD the optimiser recognise this and be expected
> > to just find the 2 row
On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd wrote:
> So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I
> propose valid and SHOULD the optimiser recognise this and be expected
> to just find the 2 rows by searching on the primary key?
Not according to the d
| transaction_history | range | PRIMARY,customer_id |
PRIMARY | 16 | NULL |2 | Using WHERE |
++-+---+---+--+-+-+--+--+-+
1 row IN set (0.02 sec)
So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I
propose valid and SHOULD the
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 the s
Perhaps pass in a "separator" string arg, default to '' and do:
GROUP_CONCAT(DISTINCT h.hostid, separator)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
I have a function that I built that returns a string that is really a
comma separated list of values (below). I would like to use that
returned value in an IN clause in sql. :
select * from hosts where hostid in (getHosts(10014));
The function:
CREATE FUNCTION getUserHosts(userID BIGINT(2
See the usage of the function named field.
On Mon, Jul 28, 2008 at 8:15 PM, Mr. Shawn H. Corey <[EMAIL PROTECTED]>wrote:
> On Mon, 2008-07-28 at 07:32 -0400, Gary Josack wrote:
> > Andrew Martin wrote:
> > > Hello,
> > >
> > > Is it permissible to order a clause such that the search term is the
>
Thanks a lot. I got the answer. "Impossible where in explian" comes when the
query doesn't make any sense to the mysql optimizer.
Please visit the link below for more details.
http://books.google.co.in/books?id=sgMvu2uZXlsC&pg=PA94&lpg=PA94&dq=impossible+where+n
other than intended recipient.
Sender does not necessarily endorse content contained within this transmission.
> Date: Tue, 2 Sep 2008 02:05:00 +0530> From: [EMAIL PROTECTED]> To: [EMAIL
PROTECTED]> Subject: Re: Impossible WHERE in explain> CC:
mysql@lists.mysql.com> > I have
I just tried this. I got the same results *until* I inserted a record that
met the criteria. SInce the values are for a primary key, EXPLAIN could see
there was no such entry and so reported that. If you actually execute the
command, you would not see an error, just no rows returned. EXPLAIN wa
I have already tried. It's not working.
On Tue, Sep 2, 2008 at 1:55 AM, Brent Baisley <[EMAIL PROTECTED]> wrote:
> It might be because you you are comparing user_id to a string, when the
> field type is a decimal. Drop the quotes around the user_id search value and
> see if that works.
>
> Brent
It might be because you you are comparing user_id to a string, when
the field type is a decimal. Drop the quotes around the user_id search
value and see if that works.
Brent Baisley
On Sep 1, 2008, at 3:59 PM, Krishna Chandra Prajapati wrote:
Hi,
In the query below explain gives 'Impossib
Hi,
In the query below explain gives 'Impossible WHERE noticed after'. what does
this mean.
CREATE TABLE `user_cookie` (
`user_id` decimal(22,0) NOT NULL default '0',
`param` varchar(128) NOT NULL default '',
`value` varchar(128) default NULL,
PRIMARY KEY (`user_id`,`param`),
CONSTRA
On Mon, 2008-07-28 at 07:32 -0400, Gary Josack wrote:
> Andrew Martin wrote:
> > Hello,
> >
> > Is it permissible to order a clause such that the search term is the
> > first item (in the clause)?
> >
> > standard:
> > field1 IN (123, 654, 789)
> >
> > in question:
> > 123 IN (field1, field2, field
Andrew Martin wrote:
Hello,
Is it permissible to order a clause such that the search term is the
first item (in the clause)?
standard:
field1 IN (123, 654, 789)
in question:
123 IN (field1, field2, field3)
I am interested to know if the optimizer treats this any differently
if anybody can she
Hello,
Is it permissible to order a clause such that the search term is the
first item (in the clause)?
standard:
field1 IN (123, 654, 789)
in question:
123 IN (field1, field2, field3)
I am interested to know if the optimizer treats this any differently
if anybody can shed any light on it (exce
Hi
If you're searching for, effectively, %123% you probably can't use IN(). You
could get away with wildcard patterns - if it's a heavily used field
consider an index to increase performance. Hard to tell if this may be a FK
of sorts, but if so you should use it as one which may involve normalisin
On Thu, 2008-07-24 at 09:34 +0530, Sivasakthi wrote:
> How can we normalize the tables? could you explain bit more?
>
>
> Thanks,
> Siva
>
>
Normalization is a complex subject. I suggest you search the web for
tutorials. Try the search terms: RDBM normalization
--
Just my 0.0002 milli
How about: WHERE ( field1 LIKE '123,%' OR field1 LIKE '*,123,%' OR
field1 LIKE '%,123' OR field1 = '123' )
Note that this could that a long time on large tables. You'd be better
off to normalize your tables ;)
How can we normalize the tables? could you explain bit more?
Thanks,
Siva
On Wed, 2008-07-23 at 17:05 +0300, Ali Deniz EREN wrote:
> Hi all,
>
> I have a problem as below:
>
> A text field -Lets call it 'field1'- contains datas seperated by
> commas(,) like this (123,5764,8795,9364,11,232,. and go on) And so
> my lines like these:
>
> id title filed1
> -
>A text field -Lets call it 'field1'- contains datas seperated by
commas(,)
> like this (123,5764,8795,9364,11,232,.
The solution is to normalise the data.
PB
Ali Deniz EREN wrote:
Hi all,
I have a problem as below:
A text field -Lets call it 'field1'- contains datas seperated by
comma
Hi all,
I have a problem as below:
A text field -Lets call it 'field1'- contains datas seperated by
commas(,) like this (123,5764,8795,9364,11,232,. and go on) And so
my lines like these:
id title filed1
-
1 title1
n [mailto:[EMAIL PROTECTED]
Sent: lundi 25 avril 2005 10:06
To: mysql@lists.mysql.com
Subject: Ordering rows whit a select from where in ( exp )
Hi everyone,
Here is my issue:
I have this Query : SELECT field_name FROM meta WHERE id
IN
('13','11',
Hi Adrian,
you can do
SELECT field_name FROM meta WHERE id IN
('13','11','7','8','9','10','12')
ORDER BY FIELD(id,'13','11','7','8','9','10','12')
/Johan
Adrian wrote:
Hi everyone,
Here is my issue:
I have this Query : SELECT field_name FROM meta WHERE id IN
Hi everyone,
Here is my issue:
I have this Query : SELECT field_name FROM meta WHERE id IN
('13','11','7','8','9','10','12')
I want the rows to be display in the same order as the in list of
ids.Any ideas? Should I use order by? Whit witch optio
> Here is my solution:
> (SELECT ID, referentie, postcode, gemeente,
> kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS
> hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID,
> referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar,
> verkoopprijs, doel AS
Jay, thx for your speedy reply.
MySQL does indeed know the "WHERE IN" syntax (bad coding on my part).
I have another question.
There are two tables in the db from which I want to gather information.
These tables are identical, except for two fields that have different
names, the same
[snip]
ANSI SQL has the WHERE key IN (value1, value2, ...)
MySQL doesn't seem to support this.
Is this a faulty assumption, or am i stuck with WHERE key = value1 OR
key = value2 ...
[/snip]
It is a faulty assumption, see
http://dev.mysql.com/doc/mysql/en/comparison-operators.html
SELECT stuff F
Greetings, list !
ANSI SQL has the WHERE key IN (value1, value2, ...)
MySQL doesn't seem to support this.
Is this a faulty assumption, or am i stuck with WHERE key = value1 OR
key = value2 ...
TIA,
Stijn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Hi Markus,
You cannot use sub-query in mysql 3.23.49
Regards,
Laercio Cavalcanti.
-Original Message-
From: Markus Källander [mailto:[EMAIL PROTECTED]
Sent: quarta-feira, 9 de junho de 2004 13:25
To: [EMAIL PROTECTED]
Subject: UPDATE...WHERE ... IN (SELECT...)
Hi all,
Can someone
On Wed, 9 Jun 2004 18:25:11 +0200
Markus Källander <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> Can someone tell me what is wrong with this statement?
>
> UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull);
>
> I use MySQL 3.23.49.
^^^
4.1 or higher is required f
> -Original Message-
> From: Markus Källander
> [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 09, 2004 10:25 AM
> To: [EMAIL PROTECTED]
> Subject: UPDATE...WHERE ... IN (SELECT...)
>
>
> Hi all,
>
> Can someone tell me what is wrong with this st
Sub-select not supported in version you are using.
-Original Message-
From: Markus Källander
To: [EMAIL PROTECTED]
Sent: 6/9/04 11:25 AM
Subject: UPDATE...WHERE ... IN (SELECT...)
Hi all,
Can someone tell me what is wrong with this statement?
UPDATE nummer SET gold = '1'
Hi all,
Can someone tell me what is wrong with this statement?
UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull);
I use MySQL 3.23.49.
Thanks
Markus Källander
dummy values).
If you're using MySQL 3.23, I've noticed that the query parser seems
much, much faster in 4.0.
Matt
- Original Message -
From: "Knepley, Jim"
Sent: Thursday, October 30, 2003 4:48 PM
Subject: WHERE IN performance
Is it anyone elses experience that queries
On Thu, 30 Oct 2003 15:48:05 -0700, Knepley, Jim <[EMAIL PROTECTED]> wrote:
Is it anyone elses experience that queries with large IN stanzas in a
WHERE clause don't scale very well?
It seems like it's beyond a linear performance hit when I have a large
number (thousands) of tokens in an IN clause,
Is it anyone elses experience that queries with large IN stanzas in a
WHERE clause don't scale very well?
It seems like it's beyond a linear performance hit when I have a large
number (thousands) of tokens in an IN clause, even when the matching
field is indexed.
Is this something that buffer twe
"a set which contains as an element
> the set itself"
>
> WHERE fooId IN (fooId)
>
> Adolfo
>
>> -Original Message-
>> From: Peter Abilla [mailto:[EMAIL PROTECTED]]
>> Sent: Monday, December 02, 2002 1:43 PM
>> To: [EMAIL PROTECTED]
>> S
002 1:43 PM
> To: [EMAIL PROTECTED]
> Subject: sql:WHERE IN SYNTAX
>
>
> Question about SQL "WHERE IN" syntax:
>
> I know that something like this is fine to do:
>
> (1)
> SELECT foo
> FROM bar
> WHERE fooID in ('1','2')
>
&
Hi.
This is mentioned in the fine manual:
http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html
Bye,
Benjamin.
On Fri, Aug 31, 2001 at 01:46:43PM +0200, [EMAIL PROTECTED] wrote:
[...]
> Normally I would go about like this:
> SELECT currency_rate.currency_id, currency_dat
I think you will you have to do it in 2 steps using a temporary table. This
is one of MySQL's biggest weak points.
..chris
- Original Message -
From: "Mattias Jiderhamn" <[EMAIL PROTECTED]>
I have a table
CREATE TABLE currency_rate (
currency_id VARCHAR(3),
currency_date DA
I have a table
CREATE TABLE currency_rate (
currency_id VARCHAR(3),
currency_date DATE,
currency_rate NUMERIC(16,8),
PRIMARY KEY (currency_id, currency_date)
);
and want to list the latest registered rates of all currencies.
Normally I would go about like this:
SELECT curr
55 matches
Mail list logo
