Re: how to select the record with one sql statement?

[shawn l.green]

Hello sea,

On 8/13/2018 7:01 PM, sea wrote:

helle,
I have a table, like this:
  pigId  dayweigt
   pig1  2018-1-121
   pig2  2018-1-131
   pig3  2018-1-141
   pig1  2018-1-222
   pig2  2018-1-231
   pig3  2018-1-240
   pig1  2018-1-323  pig2  2018-1-330
   pig3  2018-1-341
   .

only the pig1'weight increase continuously for 3 days.   Giving the input: 
num_of_day(weight increasing continuously for num_of_day);   expecting the 
output: certain_day, pigId;from certain_day,  pigId'weight increasing 
continuously for num_of_day.  How to select the records in one sql 
statement?
 thanks



I've thought about this a bit (since your question appeared on the list) 
and I break down the tasks you need to perform in my head like this. 
(Others on the list may have different ways to approach the same problem)


task 1 - For each bucket, a pigId value, assemble an ordered list (not a 
set) of each weight sorted by time.  (not hard)


task 2 - Within each ordered list, compare the values of every 
consecutive pair.  (several ways to do this)


task 3 - Iterate over those "consecutive value differences" generated in 
task 2 looking for the longest sequence of positive non-zero values for 
each pigId.  (this is not really a set-oriented process so normal SELECT 
or GROUP BY command patterns will not handle it with any efficency)


I'm afraid that attempting all of that sequencing and iteration using 
just a single set-based SQL command is not going to be practical. Using 
one or more cursors within a stored procedure is your best bet for this 
type of sequential trend analysis.


I could easily imagine the first step as a INSERT...SELECT...ORDER BY... 
command going to a new table with an autoincrement column on it (to 
provide a global sequence number across all of your individual pigId 
values) . The second step could do a self join to that table where the 
ON clause could look like

  a.pigId = b.pigID AND a.seq-1 = b.seq

But at that point, counting the length of sequences (and remembering 
when each trend became positive) needs a loop. That's where even 
complicated set-wise SQL fails you and you need to shift into using the 
SQL of stored programs.



Regards,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

how to select the record with one sql statement?

[sea]

helle,
   I have a table, like this:
 pigId  dayweigt
  pig1  2018-1-121
  pig2  2018-1-131
  pig3  2018-1-141
  pig1  2018-1-222
  pig2  2018-1-231
  pig3  2018-1-240
  pig1  2018-1-323  pig2  2018-1-330
  pig3  2018-1-341
  .

   only the pig1'weight increase continuously for 3 days.   Giving the input: 
num_of_day(weight increasing continuously for num_of_day);   expecting the 
output: certain_day, pigId;from certain_day,  pigId'weight increasing 
continuously for num_of_day.  How to select the records in one sql 
statement?
thanks

SQL statement to populate Start and End dates Everyday with an Interval of certain day(s)

[sAvEOUrplAnEt]

I had the following fields for my calendar:

   start_date = "08/01/09"  (starts on)
   end_date = "08/31/09"  (ends on)
   the_length = "1"   (everyday)
   repeat_every = "2"   (with an interval of 2 days)

Now, I want to use SQL statement to populate "daily" but with an interval of
"2" days, starting from "start_date" up to "end_date".
I was able to solve the problem on the application side, but I want to hit
it using SQL Statement.
And I was able to solved the problem without using the "repeat_every" field.

Re: the max length of one SQL statement

[Cui Shijun]

hi,
 According to the transfer limit, ... 16M?

2009/3/20 raid fifa :
> Hi guys,
>
> Anybody knows that how many bytes the max length of on SQL statement can be 
> in MySQL database?
> I know it's 64KB in Oracle.
>
> Thanks.
>
> *^_^*
>
>
>  ___
>  好玩贺卡等你发，邮箱贺卡全新上线！
> http://card.mail.cn.yahoo.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

the max length of one SQL statement

[raid fifa]

Hi guys,
 
Anybody knows that how many bytes the max length of on SQL statement can be in 
MySQL database?
I know it's 64KB in Oracle.
 
Thanks.

*^_^*


  ___ 
  好玩贺卡等你发，邮箱贺卡全新上线！ 
http://card.mail.cn.yahoo.com/

Re: Speed up slow SQL statement.

[Moon's Father]

You may see the section named group by optimization on the document.

On Tue, Sep 30, 2008 at 4:44 AM, Rob Wultsch <[EMAIL PROTECTED]> wrote:

> Glancing over things I suggest:
> ALTER TABLE browse_nodes_to_products ADD INDEX(browse_node_id,product_id);
>
> (if product_id has greater cardinality put that before browse_node_id)
>
> The syntax:
> inner join (browse_nodes, browse_nodes_to_products) on
>  (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id
> and products.id = browse_nodes_to_products.product_id)
> is pretty ugly in my opinion.
>
> On Mon, Sep 29, 2008 at 7:10 AM, Eric Stewart <[EMAIL PROTECTED]> wrote:
> > Good morning everyone,
> >
> > products.id is defined as a PRIMARY KEY so it's index.
> > browse_nodes_to_products.product_id is defined as a INDEX so it's
> indexed.
> > browse_nodes_to_products.browse_node_id is defined as an INDEX so it's
> > indexed.
> > browse_nodes.amazon_id is defined as an INDEX so it's indexed.
> >
> > See http://pastebin.com/m46cced58
> > It has complete table structures, row counts and EXPLAIN output of the
> SQL
> > statement I'm trying to optimize.
> >
> > I don't think I understand your question regarding carrying the
> product_id
> > through the relationship. This is a many to many relationship. A
> browse_node
> > can contain many products and a product can be in many browse_nodes. This
> is
> > achieved through a many to many join table browse_nodes_to_products.
> >
> > Further research into the SQL statement is revealing that a temp table is
> > being created and may be one of the reason it's slowing down.
> >
> > Any ideas how I can optimize this?
> >
> > Eric
> >
> > On Sep 26, 2008, at 11:47 AM, Martin Gainty wrote:
> >
> >>
> >> Hi Eric-
> >>
> >> the immediate challenge is to fic the join statement so
> >> make sure products.id is indexed
> >> make sure browse_nodes_to_products.product_id is indexed
> >> make sure browse_nodes_to_products.browse_node_id  is indexed
> >> make sure browse_nodes.amazon_id is indexed
> >>
> >> there seems to be mapping/relationship challenge for your product to
> >> browse_node_id
> >>
> >> which finally maps to amazon_id
> >>
> >> would be simpler if is there any way you can carry the product_id thru
> >> from products table to
> >> browser_nodes_to_products table
> >> to browse_nodes table
> >>
> >> anyone?
> >> Martin
> >> __
> >> Disclaimer and confidentiality note
> >> Everything in this e-mail and any attachments relates to the official
> >> business of Sender. This transmission is of a confidential nature and
> Sender
> >> does not endorse distribution to any party other than intended
> recipient.
> >> Sender does not necessarily endorse content contained within this
> >> transmission.
> >>
> >>
> >>> From: [EMAIL PROTECTED]
> >>> To: mysql@lists.mysql.com
> >>> Subject: Speed up slow SQL statement.
> >>> Date: Fri, 26 Sep 2008 10:42:07 -0400
> >>>
> >>> Good morning everyone,
> >>>
> >>> I've got a sql statement that is running quite slow. I've indexed
> >>> everything I can that could possibly be applicable but I can't seem to
> >>> speed it up.
> >>>
> >>> I've put up the table structures, row counts, the sql statement and
> >>> the explain dump of the sql statement all in paste online here
> >>> http://pastebin.com/m46cced58
> >>>
> >>> I'm including the sql statement itself here as well:
> >>>
> >>> select distinct products.id as id,
> >>>  products.created_at as created_at,
> >>>  products.asin as asin,
> >>>  products.sales_rank as sales_rank,
> >>>  products.points as points
> >>> from products
> >>> inner join (browse_nodes, browse_nodes_to_products) on
> >>>  (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id
> >>> and products.id = browse_nodes_to_products.product_id)
> >>> where browse_nodes.lft >= 5 and browse_nodes.rgt <= 10
> >>>  order by products.sales_rank desc limit 10 offset 0;
> >>>
> >>>
> >>> What I'm trying to accomplish with this is to get an ordered list of
> >>> unique prod

Re: Speed up slow SQL statement.

[Rob Wultsch]

Glancing over things I suggest:
ALTER TABLE browse_nodes_to_products ADD INDEX(browse_node_id,product_id);

(if product_id has greater cardinality put that before browse_node_id)

The syntax:
inner join (browse_nodes, browse_nodes_to_products) on
  (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id
and products.id = browse_nodes_to_products.product_id)
is pretty ugly in my opinion.

On Mon, Sep 29, 2008 at 7:10 AM, Eric Stewart <[EMAIL PROTECTED]> wrote:
> Good morning everyone,
>
> products.id is defined as a PRIMARY KEY so it's index.
> browse_nodes_to_products.product_id is defined as a INDEX so it's indexed.
> browse_nodes_to_products.browse_node_id is defined as an INDEX so it's
> indexed.
> browse_nodes.amazon_id is defined as an INDEX so it's indexed.
>
> See http://pastebin.com/m46cced58
> It has complete table structures, row counts and EXPLAIN output of the SQL
> statement I'm trying to optimize.
>
> I don't think I understand your question regarding carrying the product_id
> through the relationship. This is a many to many relationship. A browse_node
> can contain many products and a product can be in many browse_nodes. This is
> achieved through a many to many join table browse_nodes_to_products.
>
> Further research into the SQL statement is revealing that a temp table is
> being created and may be one of the reason it's slowing down.
>
> Any ideas how I can optimize this?
>
> Eric
>
> On Sep 26, 2008, at 11:47 AM, Martin Gainty wrote:
>
>>
>> Hi Eric-
>>
>> the immediate challenge is to fic the join statement so
>> make sure products.id is indexed
>> make sure browse_nodes_to_products.product_id is indexed
>> make sure browse_nodes_to_products.browse_node_id  is indexed
>> make sure browse_nodes.amazon_id is indexed
>>
>> there seems to be mapping/relationship challenge for your product to
>> browse_node_id
>>
>> which finally maps to amazon_id
>>
>> would be simpler if is there any way you can carry the product_id thru
>> from products table to
>> browser_nodes_to_products table
>> to browse_nodes table
>>
>> anyone?
>> Martin
>> __
>> Disclaimer and confidentiality note
>> Everything in this e-mail and any attachments relates to the official
>> business of Sender. This transmission is of a confidential nature and Sender
>> does not endorse distribution to any party other than intended recipient.
>> Sender does not necessarily endorse content contained within this
>> transmission.
>>
>>
>>> From: [EMAIL PROTECTED]
>>> To: mysql@lists.mysql.com
>>> Subject: Speed up slow SQL statement.
>>> Date: Fri, 26 Sep 2008 10:42:07 -0400
>>>
>>> Good morning everyone,
>>>
>>> I've got a sql statement that is running quite slow. I've indexed
>>> everything I can that could possibly be applicable but I can't seem to
>>> speed it up.
>>>
>>> I've put up the table structures, row counts, the sql statement and
>>> the explain dump of the sql statement all in paste online here
>>> http://pastebin.com/m46cced58
>>>
>>> I'm including the sql statement itself here as well:
>>>
>>> select distinct products.id as id,
>>>  products.created_at as created_at,
>>>  products.asin as asin,
>>>  products.sales_rank as sales_rank,
>>>  products.points as points
>>> from products
>>> inner join (browse_nodes, browse_nodes_to_products) on
>>>  (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id
>>> and products.id = browse_nodes_to_products.product_id)
>>> where browse_nodes.lft >= 5 and browse_nodes.rgt <= 10
>>>  order by products.sales_rank desc limit 10 offset 0;
>>>
>>>
>>> What I'm trying to accomplish with this is to get an ordered list of
>>> unique products found under a category.
>>>
>>> Any ideas on how I could speed this up?
>>>
>>> Thanks in advance,
>>>
>>> Eric Stewart
>>> [EMAIL PROTECTED]
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>>
>>
>> _
>> See how Windows connects the people, information, and fun that are part of
>> your life.
>> http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>



-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Speed up slow SQL statement.

[Eric Stewart]

Good morning everyone,

products.id is defined as a PRIMARY KEY so it's index.
browse_nodes_to_products.product_id is defined as a INDEX so it's  
indexed.
browse_nodes_to_products.browse_node_id is defined as an INDEX so it's  
indexed.

browse_nodes.amazon_id is defined as an INDEX so it's indexed.

See http://pastebin.com/m46cced58
It has complete table structures, row counts and EXPLAIN output of the  
SQL statement I'm trying to optimize.


I don't think I understand your question regarding carrying the  
product_id through the relationship. This is a many to many  
relationship. A browse_node can contain many products and a product  
can be in many browse_nodes. This is achieved through a many to many  
join table browse_nodes_to_products.


Further research into the SQL statement is revealing that a temp table  
is being created and may be one of the reason it's slowing down.


Any ideas how I can optimize this?

Eric

On Sep 26, 2008, at 11:47 AM, Martin Gainty wrote:



Hi Eric-

the immediate challenge is to fic the join statement so
make sure products.id is indexed
make sure browse_nodes_to_products.product_id is indexed
make sure browse_nodes_to_products.browse_node_id  is indexed
make sure browse_nodes.amazon_id is indexed

there seems to be mapping/relationship challenge for your product to  
browse_node_id


which finally maps to amazon_id

would be simpler if is there any way you can carry the product_id thru
from products table to
browser_nodes_to_products table
to browse_nodes table

anyone?
Martin
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the  
official business of Sender. This transmission is of a confidential  
nature and Sender does not endorse distribution to any party other  
than intended recipient. Sender does not necessarily endorse content  
contained within this transmission.




From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Speed up slow SQL statement.
Date: Fri, 26 Sep 2008 10:42:07 -0400

Good morning everyone,

I've got a sql statement that is running quite slow. I've indexed
everything I can that could possibly be applicable but I can't seem  
to

speed it up.

I've put up the table structures, row counts, the sql statement and
the explain dump of the sql statement all in paste online here 
http://pastebin.com/m46cced58

I'm including the sql statement itself here as well:

select distinct products.id as id,
  products.created_at as created_at,
  products.asin as asin,
  products.sales_rank as sales_rank,
  products.points as points
from products
inner join (browse_nodes, browse_nodes_to_products) on
  (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id
and products.id = browse_nodes_to_products.product_id)
where browse_nodes.lft >= 5 and browse_nodes.rgt <= 10
 order by products.sales_rank desc limit 10 offset 0;


What I'm trying to accomplish with this is to get an ordered list of
unique products found under a category.

Any ideas on how I could speed this up?

Thanks in advance,

Eric Stewart
[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



_
See how Windows connects the people, information, and fun that are  
part of your life.

http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Speed up slow SQL statement.

[Martin Gainty]

Hi Eric-

the immediate challenge is to fic the join statement so
make sure products.id is indexed
make sure browse_nodes_to_products.product_id is indexed
make sure browse_nodes_to_products.browse_node_id  is indexed
make sure browse_nodes.amazon_id is indexed

there seems to be mapping/relationship challenge for your product to 
browse_node_id

which finally maps to amazon_id

would be simpler if is there any way you can carry the product_id thru 
from products table to
browser_nodes_to_products table
to browse_nodes table

anyone?
Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 


> From: [EMAIL PROTECTED]
> To: mysql@lists.mysql.com
> Subject: Speed up slow SQL statement.
> Date: Fri, 26 Sep 2008 10:42:07 -0400
> 
> Good morning everyone,
> 
> I've got a sql statement that is running quite slow. I've indexed  
> everything I can that could possibly be applicable but I can't seem to  
> speed it up.
> 
> I've put up the table structures, row counts, the sql statement and  
> the explain dump of the sql statement all in paste online here 
> http://pastebin.com/m46cced58
> 
> I'm including the sql statement itself here as well:
> 
> select distinct products.id as id,
>products.created_at as created_at,
>products.asin as asin,
>products.sales_rank as sales_rank,
>products.points as points
> from products
> inner join (browse_nodes, browse_nodes_to_products) on
>(browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id  
> and products.id = browse_nodes_to_products.product_id)
> where browse_nodes.lft >= 5 and browse_nodes.rgt <= 10
>   order by products.sales_rank desc limit 10 offset 0;
> 
> 
> What I'm trying to accomplish with this is to get an ordered list of  
> unique products found under a category.
> 
> Any ideas on how I could speed this up?
> 
> Thanks in advance,
> 
> Eric Stewart
> [EMAIL PROTECTED]
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

_
See how Windows connects the people, information, and fun that are part of your 
life.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/

Speed up slow SQL statement.

[Eric Stewart]

Good morning everyone,

I've got a sql statement that is running quite slow. I've indexed  
everything I can that could possibly be applicable but I can't seem to  
speed it up.


I've put up the table structures, row counts, the sql statement and  
the explain dump of the sql statement all in paste online here http://pastebin.com/m46cced58


I'm including the sql statement itself here as well:

select distinct products.id as id,
  products.created_at as created_at,
  products.asin as asin,
  products.sales_rank as sales_rank,
  products.points as points
from products
inner join (browse_nodes, browse_nodes_to_products) on
  (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id  
and products.id = browse_nodes_to_products.product_id)

where browse_nodes.lft >= 5 and browse_nodes.rgt <= 10
 order by products.sales_rank desc limit 10 offset 0;


What I'm trying to accomplish with this is to get an ordered list of  
unique products found under a category.


Any ideas on how I could speed this up?

Thanks in advance,

Eric Stewart
[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with a sql statement

[paul rivers]

tech user wrote:

Add an alias for the subquery

  select * from ( select  ) my_alias where dd >= 3;

Better, use a having clause and eliminate the subquery.  Odds are it 
will be more efficient in MySQL.





How to replace the original one with a having statement?
Thanks again.
  


select uin,count(*) as dd from active_users
where date >= date_add(curdate(),interval -30 day) 
group by uin

having count(*) >=3;




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with a sql statement

[tech user]

> 
> Add an alias for the subquery
> 
>   select * from ( select  ) my_alias where dd >= 3;
> 
> Better, use a having clause and eliminate the subquery.  Odds are it 
> will be more efficient in MySQL.
> 

How to replace the original one with a having statement?
Thanks again.


  Get the name you always wanted with the new y7mail email address.
www.yahoo7.com.au/y7mail




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with a sql statement

[paul rivers]

tech user wrote:

hello,

I try to execute this sql in mysql shell,but got error as:

mysql> select *  from (select uin,count(*) as dd from active_users where
date >= date_add(curdate(),interval -30 day)  group by uin) where dd >=3;

ERROR 1248 (42000): Every derived table must have its own alias

But I can execute the sql statement of "select uin,count(*) as dd from
active_users where date >= date_add(curdate(),interval -30 day)  group by
uin" successfully.

How to fixup it? thanks!


  Get the name you always wanted with the new y7mail email address.
www.yahoo7.com.au/y7mail




  



Add an alias for the subquery

 select * from ( select  ) my_alias where dd >= 3;

Better, use a having clause and eliminate the subquery.  Odds are it 
will be more efficient in MySQL.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

help with a sql statement

[tech user]

hello,

I try to execute this sql in mysql shell,but got error as:

mysql> select *  from (select uin,count(*) as dd from active_users where
date >= date_add(curdate(),interval -30 day)  group by uin) where dd >=3;

ERROR 1248 (42000): Every derived table must have its own alias

But I can execute the sql statement of "select uin,count(*) as dd from
active_users where date >= date_add(curdate(),interval -30 day)  group by
uin" successfully.

How to fixup it? thanks!


  Get the name you always wanted with the new y7mail email address.
www.yahoo7.com.au/y7mail




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

[mysql]

[EMAIL PROTECTED] wrote:

Daevid Vincent wrote:

Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL 
puts in

plenty of other features that no other RDBMS has or uses, and other RDBMS
have features that mySQL has, so what's the problem. Unless I was porting
to/from another RDBMS?



This strikes me as a *terrible* reason.

It seems stupid that I can't do that though. I can


use the alias in the HAVING clause, and also in an ORDER BY clause.



Yes, but those are HAVING and ORDER BY clauses. You recognise that they
are distinct parts of a SELECT statement, so ...

I'm not saying it's a trivial change

So far, i'd say that you're suggesting it is.

http://dev.mysql.com/tech-resources/articles/mysql-views.html

brian



I've looked for--but cannot find--a page online that explains this 
nicely. Suffice to say that the problem is similar to that of trying to 
use a column alias in the WHERE clause. In that case, it's because WHERE 
is parsed before the SELECT clause. In your case, it's simply that the 
column expressions are constructed more or less as one, not from left to 
right or anything like that.


You could also use a derived table here, i should think:

SELECT foo.bar, UNIX_TIMESTAMP(foo.bar) FROM (
  SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
  AS bar
  FROM end_user_groups
) AS foo;

brian

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

[mysql]

Daevid Vincent wrote:

Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL puts in
plenty of other features that no other RDBMS has or uses, and other RDBMS
have features that mySQL has, so what's the problem. Unless I was porting
to/from another RDBMS?


This strikes me as a *terrible* reason.

It seems stupid that I can't do that though. I can

use the alias in the HAVING clause, and also in an ORDER BY clause.


Yes, but those are HAVING and ORDER BY clauses. You recognise that they
are distinct parts of a SELECT statement, so ...

I'm not saying it's a trivial change

So far, i'd say that you're suggesting it is.

http://dev.mysql.com/tech-resources/articles/mysql-views.html

brian

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

[Daevid Vincent]

Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL puts in
plenty of other features that no other RDBMS has or uses, and other RDBMS
have features that mySQL has, so what's the problem. Unless I was porting
to/from another RDBMS? It seems stupid that I can't do that though. I can
use the alias in the HAVING clause, and also in an ORDER BY clause. I'm not
saying it's a trivial change, I am saying that it would make a lot of sense
to do and I'm sure I'm not the first person to desire such a feature.

d

> -Original Message-
> From: Peter Brawley [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 23, 2007 5:39 PM
> To: Daevid Vincent
> Cc: mysql@lists.mysql.com
> Subject: Re: Why can't I still not use an ALIAS in the SELECT 
> portion of an SQL statement?
> 
> >When will I be able to do something seemingly 
> >so basic as this re-use of an alias?
> 
> Do you know an implementation of SQL which allows this?
> 
> PB
> 
> Daevid Vincent wrote:
> > When will I be able to do something seemingly so basic as 
> this re-use of an
> > alias?
> >
> > SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR) 
> > AS group_duration_date,
> >  UNIX_TIMESTAMP(group_duration_date)
> > AS group_duration_date_timestamp
> > FROM end_user_groups;
> >
> > Error Code : 1054
> > Unknown column 'group_duration_date' in 'field list'
> > (0 ms taken)
> >
> > So instead I must do this very cumbersome and inefficient way:
> >
> > SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR) 
> > AS group_duration_date,
> >  UNIX_TIMESTAMP(DATE_ADD('2007-10-23', 
> > INTERVAL 
> user_access_hours HOUR)) 
> > AS group_duration_date_timestamp
> > FROM end_user_groups;
> >
> > *sigh*
> >
> > This has been a cause of frustration since mysql 3.x series. 
> > Are there any plans to fix this annoyance? If so, in what version?
> >
> > Currently using: 
> > mysql  Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)
> >
> >
> >   
> 
> -- 
> 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: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

[Peter Brawley]

When will I be able to do something seemingly 
so basic as this re-use of an alias?


Do you know an implementation of SQL which allows this?

PB

Daevid Vincent wrote:

When will I be able to do something seemingly so basic as this re-use of an
alias?

SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR) 
		AS group_duration_date,

 UNIX_TIMESTAMP(group_duration_date)
AS group_duration_date_timestamp
FROM end_user_groups;

Error Code : 1054
Unknown column 'group_duration_date' in 'field list'
(0 ms taken)

So instead I must do this very cumbersome and inefficient way:

SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR) 
		AS group_duration_date,
	 UNIX_TIMESTAMP(DATE_ADD('2007-10-23', 
	INTERVAL user_access_hours HOUR)) 
		AS group_duration_date_timestamp

FROM end_user_groups;

*sigh*

This has been a cause of frustration since mysql 3.x series. 
Are there any plans to fix this annoyance? If so, in what version?


Currently using: 
mysql  Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)



  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

[Daevid Vincent]

When will I be able to do something seemingly so basic as this re-use of an
alias?

SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR) 
AS group_duration_date,
 UNIX_TIMESTAMP(group_duration_date)
AS group_duration_date_timestamp
FROM end_user_groups;

Error Code : 1054
Unknown column 'group_duration_date' in 'field list'
(0 ms taken)

So instead I must do this very cumbersome and inefficient way:

SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR) 
AS group_duration_date,
 UNIX_TIMESTAMP(DATE_ADD('2007-10-23', 
INTERVAL user_access_hours HOUR)) 
AS group_duration_date_timestamp
FROM end_user_groups;

*sigh*

This has been a cause of frustration since mysql 3.x series. 
Are there any plans to fix this annoyance? If so, in what version?

Currently using: 
mysql  Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL statement work in mysql4 but not mysql5

[Peter Brawley]

Jason

following statement works in 4 but not 5

SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
c.image, c.url, m.member_name as mod_name, m.member_id as
mod_id, m.is_group, m.group_id, m.group_name, m.mid
FROM ibf_forums f, ibf_categories c
  LEFT JOIN ibf_moderators m ON (f.id=m.forum_id)
WHERE c.id=f.category
ORDER BY c.position, f.position

Look up joins in the 5.0 or 5.1 manual. As of 5.0.12, MySQL implemented 
ANSI/ISO compliance, breaking comma joins of the sort you use above. You 
need explicit JOIN ... ON | USING syntax to remove referential ambiguities.


PB

-

Jason Chan wrote:

I am going to upgrade my database from version 4 to 5.
However I found some of my web application doesn't work on MySQL5

e.g following statement works in 4 but not 5

SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
 c.image, c.url, m.member_name as mod_name, m.member_id as
mod_id, m.is_group, m.group_id, m.group_name, m.mid
 FROM ibf_forums f, ibf_categories c
   LEFT JOIN ibf_moderators m ON (f.id=m.forum_id)
 WHERE c.id=f.category
 ORDER BY c.position, f.position

Error:
Unknown column 'f.id' in 'on clause'

The alias seem not working?
What should I do, I dont want to rewrite all my sql statement

Thanks.

Jason





  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.1/470 - Release Date: 10/10/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL statement work in mysql4 but not mysql5

[Jo�o C�ndido de Souza Neto]

I had this problem here and i change my query to:

SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
 c.image, c.url, m.member_name as mod_name, m.member_id as
mod_id, m.is_group, m.group_id, m.group_name, m.mid
 FROM ibf_forums f
   INNER JOIN ibf_categories c on f.id_cat=c.id_cat
   LEFT JOIN ibf_moderators m ON (f.id=m.forum_id)
 WHERE c.id=f.category
 ORDER BY c.position, f.posit

It works fine to me.



""Jason Chan"" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
>I am going to upgrade my database from version 4 to 5.
> However I found some of my web application doesn't work on MySQL5
>
> e.g following statement works in 4 but not 5
>
> SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
> cat_state, c.name as cat_name, c.description as cat_desc,
> c.image, c.url, m.member_name as mod_name, m.member_id as
> mod_id, m.is_group, m.group_id, m.group_name, m.mid
> FROM ibf_forums f, ibf_categories c
>   LEFT JOIN ibf_moderators m ON (f.id=m.forum_id)
> WHERE c.id=f.category
> ORDER BY c.position, f.position
>
> Error:
> Unknown column 'f.id' in 'on clause'
>
> The alias seem not working?
> What should I do, I dont want to rewrite all my sql statement
>
> Thanks.
>
> Jason
>
>
>
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SQL statement work in mysql4 but not mysql5

[Jason Chan]

I am going to upgrade my database from version 4 to 5.
However I found some of my web application doesn't work on MySQL5

e.g following statement works in 4 but not 5

SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
 c.image, c.url, m.member_name as mod_name, m.member_id as
mod_id, m.is_group, m.group_id, m.group_name, m.mid
 FROM ibf_forums f, ibf_categories c
   LEFT JOIN ibf_moderators m ON (f.id=m.forum_id)
 WHERE c.id=f.category
 ORDER BY c.position, f.position

Error:
Unknown column 'f.id' in 'on clause'

The alias seem not working?
What should I do, I dont want to rewrite all my sql statement

Thanks.

Jason





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: The number of left join in one SQL statement.

[Pooly]

Hi,

2006/6/16, Takanobu Kawabe <[EMAIL PROTECTED]>:




[snip]

I tried  this statement  without error.

But Ihave some questions.


1.How  many  left join  keywords   can  I   use   in  one SQL statement  if
there
are   5000 datas in  one  table?


as this blog point out, its 31 or 61 depending on your version :
http://mike.kruckenberg.com/archives/2006/06/limit_on_number.html
And the number of rows in the table doen't have to do with this limit
(only the query time :-).

--
http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: The number of left join in one SQL statement.

[Barry]

Takanobu Kawabe schrieb:


> Hello, my  name  is  Takanobu  Kawabe.
こんいちわ孝信さん：）

> I  have some questions  about  left join  SQL statement.
> 
> I want to join some  tables, and  using  left  join  statement .
> 
> the  system is the following.  I use two machines.
> 
> I  created  the  same  databases  and  tables  on  both machines.
> 
> OS ： Debian Linux the newest  stable 3.1
> Server ： Apache 2.0
> MySQL ： 5.0.20
> PHP ： 5.1.4
> 
> OS ： WindowsXP
> Server ： Apache2.0
> MySQL ： 5.0.19
> PHP ： 5.1.2
> 
> 
> 
> And  I  considered  the following SQL statement to select  some columns from 
> some tables  using  left  join.
> 
> 
> 
> SELECT
> `MST`.`no`,
> `MST`.`denhyono`,
> `GUS`.`TANI` AS `GUS`,
> `SUIDO`.`TANI` AS `SUIDO`,
> `DENKI`.`TANI` AS `DENKI`,
> `SYOKUIN1`.`SYOKUINNAME` AS `SYOKUIN1`,
> `SYOKUIN2`.`SYOKUINNAME` AS `SYOKUIN2`,
> `TANTOSYA`.`TANTOSYANAME` AS `TANTOSYA`
> FROM
> `MST`
> Left Join `TANIMST` AS `GUS` ON `MST`.`GUSno` = `GUS`.`no`
> Left Join `TANIMST` AS `SUIDO` ON `MST`.`SUIDOno` = `SUIDO`.`no`
> Left Join `TANIMST` AS `DENKI` ON `MST`.`DENKIno` = `DENKI`.`no`
> Left Join `SYOKUINMST` AS `SYOKUIN1` ON `MST`.`SYOKUINno1` = `SYOKUIN1`.`no`
> Left Join `SYOKUINMST` AS `SYOKUIN2` ON `MST`.`SYOKUINno2` = `SYOKUIN2`.`no`
> Left Join `SYOKUINMST` AS `TANTOSYA` ON `MST`.`TANTOSYAno` = 
> `TANTOSYA`.`no`;
> 
> -
> I tried  this statement  without error.
> 
> But Ihave some questions.
> 
> 
> 1.How  many  left join  keywords   can  I   use   in  one SQL statement  if 
> there
> are   5000 datas in  one  table?
As much as you like. there are no limitations.

> 2. when  some customers  use  this  query,  can  they  read  this  query 
> exactly?
Depends on the MySQL knowledge of your customer.
But when they are good in SQL they shouldn't have any problem with it.

> 3. Will  the  above  query  speed  become slowly  if   there  are   5000 
> datas in  one  table?
Set indizes. This way your queries will speed up.

> Will the processing efficiency  improve  if  I  use   multiple SQL 
> statement?

Always depends on the process.
There are processes where multiple SQL queries come in handy.


> 4.Is  there  any  other  effective  SQL  statement   I  can   get  better 
> processing efficiency?

The beast way would be to start reading the optimization of MySQL:
http://dev.mysql.com/doc/refman/5.0/en/optimization.html

Here you get a lot of helpful omtimizations for your queries.

それじゃ。

Barry
-- 
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

The number of left join in one SQL statement.

[Takanobu Kawabe]

Hello, my  name  is  Takanobu  Kawabe.

I  have some questions  about  left join  SQL statement.

I want to join some  tables, and  using  left  join  statement .

the  system is the following.  I use two machines.

I  created  the  same  databases  and  tables  on  both machines.

OS ： Debian Linux the newest  stable 3.1
Server ： Apache 2.0
MySQL ： 5.0.20
PHP ： 5.1.4

OS ： WindowsXP
Server ： Apache2.0
MySQL ： 5.0.19
PHP ： 5.1.2



And  I  considered  the following SQL statement to select  some columns from 
some tables  using  left  join.



SELECT
`MST`.`no`,
`MST`.`denhyono`,
`GUS`.`TANI` AS `GUS`,
`SUIDO`.`TANI` AS `SUIDO`,
`DENKI`.`TANI` AS `DENKI`,
`SYOKUIN1`.`SYOKUINNAME` AS `SYOKUIN1`,
`SYOKUIN2`.`SYOKUINNAME` AS `SYOKUIN2`,
`TANTOSYA`.`TANTOSYANAME` AS `TANTOSYA`
FROM
`MST`
Left Join `TANIMST` AS `GUS` ON `MST`.`GUSno` = `GUS`.`no`
Left Join `TANIMST` AS `SUIDO` ON `MST`.`SUIDOno` = `SUIDO`.`no`
Left Join `TANIMST` AS `DENKI` ON `MST`.`DENKIno` = `DENKI`.`no`
Left Join `SYOKUINMST` AS `SYOKUIN1` ON `MST`.`SYOKUINno1` = `SYOKUIN1`.`no`
Left Join `SYOKUINMST` AS `SYOKUIN2` ON `MST`.`SYOKUINno2` = `SYOKUIN2`.`no`
Left Join `SYOKUINMST` AS `TANTOSYA` ON `MST`.`TANTOSYAno` = 
`TANTOSYA`.`no`;

-
I tried  this statement  without error.

But Ihave some questions.


1.How  many  left join  keywords   can  I   use   in  one SQL statement  if 
there
are   5000 datas in  one  table?

2. when  some customers  use  this  query,  can  they  read  this  query 
exactly?

3. Will  the  above  query  speed  become slowly  if   there  are   5000 
datas in  one  table?
Will the processing efficiency  improve  if  I  use   multiple SQL 
statement?

4.Is  there  any  other  effective  SQL  statement   I  can   get  better 
processing efficiency?

Re: max_allowed_packet via SQL Statement

[Dan Nelson]

In the last episode (May 18), Jim said:
> Is there any way of getting the max_allowed_packet setting via a SQL
> statement?

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
++--+
| Variable_name  | Value|
++--+
| max_allowed_packet | 10484736 | 
++--+
1 row in set (0.00 sec)

or:

mysql> select @@max_allowed_packet;
+--+
| @@max_allowed_packet |
+--+
| 10484736 |
+--+
1 row in set (0.00 sec)

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

max_allowed_packet via SQL Statement

[Jim]

 

Hi All,

 

Is there any way of getting the max_allowed_packet setting via a SQL
statement?

 

Eg. 

select version(); returns the version and need to do same for
max_packet_size.

 

Re: Optimizing SQL statement

[Puiu Hrenciuc]

First of all thanks all for your answers.
Second I'll explain what I manage to do in regard with this issue,
maybe someone else may need it in the future.

So, first I have started by changing the way I have stored the `ip`
field from varchar(15) to int unsigned and populated this field with
the 4 bytes value of the ip ( INET_ATON ). Then I have done some
benchmarking using the varchar field and ORDER BY NULL to avoid
sorting. After that I have done some benchmarks using the ip stored as
numbers ( of course I have redefined the primary key, etc, etc ). I was
surprised to find out that grouping by a varchar was FASTER then by an
int column. I was surprised to find out this because my logic tells me that
it should be easyer to group by a 4 byte data than by a 15 bytes data.
The tests were done using same computer, same database engine.
After that, I also tryed to convert the table to InnoDB ( originally it was
MyISAM ) and made same tests ( ip as int and as varchar ). The results
were completly different, the grouping by ip as int was faster than ip
as varchar and overall both queries were faster than if executed against
a MyISAM table. I decided to keep the IP as INT and table as InnoDB,
the time is now reduced to 2.3s ( avg ), still too much, but faster than
before anyway. I think table partitions from 5.1 will help by splitting 
records
by year and month , but waiting to get stable though.

Queries used :

For IP as VARCHAR :

SELECT `ip`, `type`,SUM(`inbound`) AS `in`, SUM(`outbound`) AS`out`
FROM `accounting`
GROUP BY `ip`,`type`
WHERE `record_time` BETWEEN '2006010100' AND '2006020100'
ORDER BY NULL

For IP as INT UNSIGNED :

SELECT INET_NTOA(`ip`) AS `ip`, `type`,SUM(`inbound`) AS `in`, 
SUM(`outbound`) AS`out`
FROM `accounting`
GROUP BY `ip`,`type`
WHERE `record_time` BETWEEN '2006010100' AND '2006020100'
ORDER BY NULL

Results :

table_engine  ip_type   avg_query_time (s)
MyISAMVARCHAR(15)  6.7
MyISAMINT UNSIGNED 9.4
InnoDB  VARCHAR(15) 2.8
InnoDB  INT UNSIGNED2.3

That would be all, thanks again.

Puiu Hrenciuc

P.S.: Removing ORDER BY NULL adds "using filesort" and takes longer.


"Joerg Bruehe" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> Hi all!
>
> (Sorry for the late reply.)
>
> Puiu Hrenciuc wrote (re-ordered):
>> "Barry" <[EMAIL PROTECTED]> wrote in message 
>> news:[EMAIL PROTECTED]
>>> Puiu Hrenciuc wrote:
 Hi,

 I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network 
 traffic data :

 record_time datetime  - time when recording was added
 ip char(15) - ip that generated the traffic
 type tinyint(3) - traffic type ( 1 - local, 2 - internet )
 inbound int(10) - in bytes
 outbound int(10) - out bytes

 Records are inserted each 5 minutes through a cron script.
 Currently there are 3,330,367 rows.

 Primary index is defined on ( ip, type, record_time ), columns in that 
 order.
 Also there is an index defined only on record_time

 Now for an example to get traffic for this month, I use :

 SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out`
 FROM `accounting`
 WHERE `record_time` BETWEEN 2006040100 AND 2006041316
 GROUP BY `ip`,`type`

 this query takes aprox 7 seconds

 Using EXPLAIN gives :

 select_typetable  type  possible_keys key 
 key_len  ref  rows  Extra
 SIMPLE accounting   range   record_time record_time 8 
 NULL 362410 Using where; Using temporary; Using filesort

 If I remove the SUM functions I am getting also "Using index in 
 group-by"
 and the query takes only 0.25 sec

 Is there anyway to optimize this query to get faster responses ?
>
>
> For the original query, the index on "record_time" can be used to select 
> the records to be considered, and the base table must be accessed to sum 
> the "inbound" and "outbound" values.
>
> Without the summing, all information needed for the answer is in the 
> primary key, so an "index-only" strategy can be used (does not need the 
> base table, profits from key cache, ...).
> Also, by the "group by" clause each distinct combination of (ip, type) is 
> needed only once, so the amount of data that need to be handled is much 
> smaller.
>
> The summing must make a big difference, this cannot be avoided.
>
> You _might_ try an index (record_time, ip, type), because here the leading 
> (= most significant) part can be used for your "where condition", and the 
> next components support the "group by", so there is a slight chance to 
> avoid the sorting.
> Disclaimer: This is pure speculation from my part!
>
>

>>> Set an index on ip and type and "probably" also on record

Re: Optimizing SQL statement

[Joerg Bruehe]

Hi all!

(Sorry for the late reply.)

Puiu Hrenciuc wrote (re-ordered):
"Barry" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]

Puiu Hrenciuc wrote:

Hi,

I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network 
traffic data :


record_time datetime  - time when recording was added
ip char(15) - ip that generated the traffic
type tinyint(3) - traffic type ( 1 - local, 2 - internet )
inbound int(10) - in bytes
outbound int(10) - out bytes

Records are inserted each 5 minutes through a cron script.
Currently there are 3,330,367 rows.

Primary index is defined on ( ip, type, record_time ), columns in that 
order.

Also there is an index defined only on record_time

Now for an example to get traffic for this month, I use :

SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out`
FROM `accounting`
WHERE `record_time` BETWEEN 2006040100 AND 2006041316
GROUP BY `ip`,`type`

this query takes aprox 7 seconds

Using EXPLAIN gives :

select_typetable  type  possible_keys key 
key_len  ref  rows  Extra
SIMPLE accounting   range   record_time record_time 8 
NULL 362410 Using where; Using temporary; Using filesort


If I remove the SUM functions I am getting also "Using index in group-by"
and the query takes only 0.25 sec

Is there anyway to optimize this query to get faster responses ?



For the original query, the index on "record_time" can be used to select 
the records to be considered, and the base table must be accessed to sum 
the "inbound" and "outbound" values.


Without the summing, all information needed for the answer is in the 
primary key, so an "index-only" strategy can be used (does not need the 
base table, profits from key cache, ...).
Also, by the "group by" clause each distinct combination of (ip, type) 
is needed only once, so the amount of data that need to be handled is 
much smaller.


The summing must make a big difference, this cannot be avoided.

You _might_ try an index (record_time, ip, type), because here the 
leading (= most significant) part can be used for your "where 
condition", and the next components support the "group by", so there is 
a slight chance to avoid the sorting.

Disclaimer: This is pure speculation from my part!





Set an index on ip and type and "probably" also on record_time

>
> Hmmm, I have omited that :
>
> I also have an index on (ip,type) in that order


The index on (ip,type) is a prefix of the primary key (ip, type, 
record_time), and in general any prefix of an existing key can be used.


In other words: I doubt whether there are any statements that make use 
of this additional index but would not make the same use of the primary key.
But as an index needs to be maintained on each insert/update/delete, IMO 
all this additional index does is slow down these statements (and take 
up disk space).


You might reduce workload by dropping that index, but it has no effect 
on the two queries (except reduced file size, amount of data, and all 
these general consequences).



Regards,
Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
Office:  (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimizing SQL statement

[Philippe Poelvoorde]

2006/4/13, Puiu Hrenciuc <[EMAIL PROTECTED]>:
> Hi,
>
> I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic
> data :
>
> record_time datetime  - time when recording was added
> ip char(15) - ip that generated the traffic
> type tinyint(3) - traffic type ( 1 - local, 2 - internet )
> inbound int(10) - in bytes
> outbound int(10) - out bytes
>
> Records are inserted each 5 minutes through a cron script.
> Currently there are 3,330,367 rows.
>
> Primary index is defined on ( ip, type, record_time ), columns in that
> order.
> Also there is an index defined only on record_time

You don't need a char(15) to store the IP. an unsigned integer should be enough.
there is INET_ATON and INET_NTOA to do the convertion.
http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html
You'll save 11 bytes ! index will be smaller, and it'll give you a
better response time I think.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimizing SQL statement

[Puiu Hrenciuc]

Hmmm, I have omited that :

I also have an index on (ip,type) in that order

"Barry" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> Puiu Hrenciuc wrote:
>> Hi,
>>
>> I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network 
>> traffic data :
>>
>> record_time datetime  - time when recording was added
>> ip char(15) - ip that generated the traffic
>> type tinyint(3) - traffic type ( 1 - local, 2 - internet )
>> inbound int(10) - in bytes
>> outbound int(10) - out bytes
>>
>> Records are inserted each 5 minutes through a cron script.
>> Currently there are 3,330,367 rows.
>>
>> Primary index is defined on ( ip, type, record_time ), columns in that 
>> order.
>> Also there is an index defined only on record_time
>>
>> Now for an example to get traffic for this month, I use :
>>
>> SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out`
>> FROM `accounting`
>> WHERE `record_time` BETWEEN 2006040100 AND 2006041316
>> GROUP BY `ip`,`type`
>>
>> this query takes aprox 7 seconds
>>
>> Using EXPLAIN gives :
>>
>> select_typetable  type  possible_keys key 
>> key_len  ref  rows  Extra
>> SIMPLE accounting   range   record_time record_time 8 
>> NULL 362410 Using where; Using temporary; Using filesort
>>
>> If I remove the SUM functions I am getting also "Using index in group-by"
>> and the query takes only 0.25 sec
>>
>> Is there anyway to optimize this query to get faster responses ?
>>
>> Thanks,
>> ---
>> Puiu Hrenciuc
> Set an index on ip and type and "probably" also on record_time
>
> Barry
>
> -- 
> Smileys rule (cX.x)C --o(^_^o)
> Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimizing SQL statement

[Barry]

Puiu Hrenciuc wrote:

Hi,

I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic 
data :


record_time datetime  - time when recording was added
ip char(15) - ip that generated the traffic
type tinyint(3) - traffic type ( 1 - local, 2 - internet )
inbound int(10) - in bytes
outbound int(10) - out bytes

Records are inserted each 5 minutes through a cron script.
Currently there are 3,330,367 rows.

Primary index is defined on ( ip, type, record_time ), columns in that 
order.

Also there is an index defined only on record_time

Now for an example to get traffic for this month, I use :

SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out`
FROM `accounting`
WHERE `record_time` BETWEEN 2006040100 AND 2006041316
GROUP BY `ip`,`type`

this query takes aprox 7 seconds

Using EXPLAIN gives :

select_typetable  type  possible_keys key 
key_len  ref  rows  Extra
SIMPLE accounting   range   record_time record_time 
8 NULL 362410 Using where; Using temporary; Using filesort


If I remove the SUM functions I am getting also "Using index in group-by"
and the query takes only 0.25 sec

Is there anyway to optimize this query to get faster responses ?

Thanks,
---
Puiu Hrenciuc 




Set an index on ip and type and "probably" also on record_time

Barry

--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Optimizing SQL statement

[Puiu Hrenciuc]

Hi,

I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic 
data :

record_time datetime  - time when recording was added
ip char(15) - ip that generated the traffic
type tinyint(3) - traffic type ( 1 - local, 2 - internet )
inbound int(10) - in bytes
outbound int(10) - out bytes

Records are inserted each 5 minutes through a cron script.
Currently there are 3,330,367 rows.

Primary index is defined on ( ip, type, record_time ), columns in that 
order.
Also there is an index defined only on record_time

Now for an example to get traffic for this month, I use :

SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out`
FROM `accounting`
WHERE `record_time` BETWEEN 2006040100 AND 2006041316
GROUP BY `ip`,`type`

this query takes aprox 7 seconds

Using EXPLAIN gives :

select_typetable  type  possible_keys key 
key_len  ref  rows  Extra
SIMPLE accounting   range   record_time record_time 
8 NULL 362410 Using where; Using temporary; Using filesort

If I remove the SUM functions I am getting also "Using index in group-by"
and the query takes only 0.25 sec

Is there anyway to optimize this query to get faster responses ?

Thanks,
---
Puiu Hrenciuc 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Update or insert with a single SQL statement?

[Shaunak Kashyap]

Or the more standard INSERT... ON DUPLICATE KEY UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Shaunak Kashyap
 
Senior Web Developer
WPT Enterprises, Inc.
5700 Wilshire Blvd., Suite 350
Los Angeles, CA 90036
 
Direct: 323.330.9870
Main: 323.330.9900
 
www.worldpokertour.com
 
Confidentiality Notice:  This e-mail transmission (and/or the
attachments accompanying) it may contain confidential information
belonging to the sender which is protected.  The information is intended
only for the use of the intended recipient.  If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution or taking of any action in reliance on the contents of this
information is prohibited. If you have received this transmission in
error, please notify the sender by reply e-mail and destroy all copies
of this transmission.


> -Original Message-
> From: Greg Donald [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 31, 2006 2:38 PM
> To: mysql@lists.mysql.com
> Subject: Re: Update or insert with a single SQL statement?
> 
> On 3/31/06, Brian Dunning <[EMAIL PROTECTED]> wrote:
> > I have a really simple two-column database:
> >
> > domain_name (primary key)
> > timestamp
> >
> > I'm trying to keep track of the referrer of every visit to a web
> > site, and I'm looking for a single SQL statement (since my ISP
limits
> > the total number of calls I can make in a day) that will either
> > insert a new record if the referring domain is not already in there,
> > or simply update the timestamp if the referring domain is already in
> > there.
> >
> > Possible?
> 
> REPLACE INTO
> 
> http://dev.mysql.com/doc/refman/5.0/en/replace.html
> 
> 
> --
> Greg Donald
> Zend Certified Engineer
> MySQL Core Certification
> http://destiney.com/
> 
> --
> 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: Update or insert with a single SQL statement?

[Greg Donald]

On 3/31/06, Brian Dunning <[EMAIL PROTECTED]> wrote:
> I have a really simple two-column database:
>
> domain_name (primary key)
> timestamp
>
> I'm trying to keep track of the referrer of every visit to a web
> site, and I'm looking for a single SQL statement (since my ISP limits
> the total number of calls I can make in a day) that will either
> insert a new record if the referring domain is not already in there,
> or simply update the timestamp if the referring domain is already in
> there.
>
> Possible?

REPLACE INTO

http://dev.mysql.com/doc/refman/5.0/en/replace.html


--
Greg Donald
Zend Certified Engineer
MySQL Core Certification
http://destiney.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update or insert with a single SQL statement?

[Brian Dunning]

I have a really simple two-column database:

domain_name (primary key)
timestamp

I'm trying to keep track of the referrer of every visit to a web  
site, and I'm looking for a single SQL statement (since my ISP limits  
the total number of calls I can make in a day) that will either  
insert a new record if the referring domain is not already in there,  
or simply update the timestamp if the referring domain is already in  
there.


Possible?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I'm glad to hear that your data isn't corrupt! That would have complicated 
your life a bit, at least in the short term


The additional information you have supplied helps me understand a bit 
better but I still don't really understand enough. I'll try to ask some 
specific questions that will help me understand the data and what you are 
trying to do better.


1. What kind of join are you doing to combine these tables? Is it an inner 
join or some kind of outer join? Are you satisfied that it is correctly 
joining the tables and giving you a true picture of the different events 
affecting the patients? I just want to be sure that the join is giving the 
right data before we go any farther; if it isn't, we should fix the join 
first.


2. Does the id belong to a specific patient? For example, does id 2 belong 
to Tony Blair while id 3 belongs to Jacques Chirac? I think this must be the 
case, but I want to be sure.


3. What do you mean by an 'event'? Is this a surgical procedure like "remove 
appendix" or just something like emptying a bedpan? What exactly is event 4? 
If it's secret, that's okay but it would help me understand the problem 
better to know what event 4 and a some of the other events are. Can a 
patient have more than one event 4? For example, if event 4 is "empty 
patient's bedpan" that can probably happen many times but  if event 4 is 
"patient died", that can obviously only happen once.


4. How do you propose to determine the time difference between events when 
you aren't storing the times that the events took place??? You described the 
record number (recno) as a simple ascending integer earlier but now I wonder 
if you mean that it is actually a timestamp or datetime value? Otherwise, I 
don't see how an expression like 7 - 4 (for records 7 and 4) is going to 
give you a value like 2 hours and 10 minutes.


5. What do you mean when you described eType as "nominal" and not "interval" 
data?


--
Rhino

- Original Message ----- 
From: "Søren Merser" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, March 05, 2006 4:37 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Hi
Thank You for all Your efforts
I'll try once again to clarify my problem

My tabel (t_temp) isn't corrupt but is the result form a join from two 
other tables

1) t_base, with id's and basic info of patients.
2) t_events. with id (of the patient in question) and the type of the 
event.


As a patient, or id can have more than one event t_temp will end up with 
one or more records accordingly

if no event has occured for id, etype=NULL

t_temp:

id  |   etype
-
1|NULL
2|4
2|6
3|NULL
4|NULL
5|1
5|3
6|7
6|3

Now I want to examine for the event of etype=4, in particular the time 
between the events.
I want to extract all the id's with etype=4 i.e. the event in question has 
happend othervise NULL.

Order must be preserved.
Result: the number of records will mirror the total number of operations 
and the actual record number mirrors the time of the event



id  |   etype
-
1|NULL
2|4
3|NULL
4|NULL
5|NULL
6|NULL

6 operations in all, second operation had event 4

Kind regards Soren
Ps
1)
As I make subsets from t_temp depending other columns, the id's do not 
exactly indicate time of events

2)
etype is nominal, not inteval data so You can't use < or > operator


- Original Message - 
From: "Rhino" <[EMAIL PROTECTED]>

To: "Michael Stassen" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 9:05 PM
Subject: Re: Help on sql statement (not MySQL specifik)


You're absolutely right that I'd need some good luck for this query to 
work for every possible data value that the table could continue.


I realized the combination of 'group by id' and 'select id, type' was not 
very good SQL - 'select id, ' would be a much more 
standard construction to go with 'group by id' - as I developed that 
query. But I was too lazy to dig through the manual to find out exactly 
what MySQL would do with that query; it worked fine for the data given. 
But you're right, I should have at least warned that this was dubious SQL 
before posting it. The original poster could easily have though that this 
was actually good SQL when it isn't.


Again, cleaning up the data (assuming it is messed up!) should be the 
first priority and any query would just be a bandaid until that is done. 
The query would probably be a lot easier if the data was clean to start 
with.


In any case, thanks for keeping me honest.

--
R

Re: Help on sql statement (not MySQL specifik)

[Søren Merser]

Hi
Thank You for all Your efforts
I'll try once again to clarify my problem

My tabel (t_temp) isn't corrupt but is the result form a join from two other 
tables

1) t_base, with id's and basic info of patients.
2) t_events. with id (of the patient in question) and the type of the event.

As a patient, or id can have more than one event t_temp will end up with one 
or more records accordingly

if no event has occured for id, etype=NULL

t_temp:

id  |   etype
-
1|NULL
2|4
2|6
3|NULL
4|NULL
5|1
5|3
6|7
6|3

Now I want to examine for the event of etype=4, in particular the time 
between the events.
I want to extract all the id's with etype=4 i.e. the event in question has 
happend othervise NULL.

Order must be preserved.
Result: the number of records will mirror the total number of operations and 
the actual record number mirrors the time of the event



id  |   etype
-
1|NULL
2|4
3|NULL
4|NULL
5|NULL
6|NULL

6 operations in all, second operation had event 4

Kind regards Soren
Ps
1)
As I make subsets from t_temp depending other columns, the id's do not 
exactly indicate time of events

2)
etype is nominal, not inteval data so You can't use < or > operator


- Original Message - 
From: "Rhino" <[EMAIL PROTECTED]>

To: "Michael Stassen" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 9:05 PM
Subject: Re: Help on sql statement (not MySQL specifik)


You're absolutely right that I'd need some good luck for this query to 
work for every possible data value that the table could continue.


I realized the combination of 'group by id' and 'select id, type' was not 
very good SQL - 'select id, ' would be a much more 
standard construction to go with 'group by id' - as I developed that 
query. But I was too lazy to dig through the manual to find out exactly 
what MySQL would do with that query; it worked fine for the data given. 
But you're right, I should have at least warned that this was dubious SQL 
before posting it. The original poster could easily have though that this 
was actually good SQL when it isn't.


Again, cleaning up the data (assuming it is messed up!) should be the 
first priority and any query would just be a bandaid until that is done. 
The query would probably be a lot easier if the data was clean to start 
with.


In any case, thanks for keeping me honest.

--
Rhino

- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>

To: "Rhino" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 2:26 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Rhino wrote:
I don't really understand _why_ you want to do this but here is a query 
that gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave 
it alone, otherwise display null.


--
Rhino


Unfortunately, that won't work unless you are very lucky.  You aren't 
grouping by type, and CASE is not an aggregate function.  Mysql will use 
the value for type from the first row it finds for each id in the CASE 
statement.  The following illustrate the problem:


  DROP TABLE nu;
  CREATE TABLE nu (recno INT, id INT, type INT);
  INSERT INTO nu VALUES
  (1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);

  SELECT * FROM nu;
+---+--+--+
| recno | id   | type |
+---+--+--+
| 1 |1 | NULL |
| 2 |2 |4 |
| 3 |2 |6 |
| 4 |3 |5 |
| 5 |3 |4 |
| 6 |3 |3 |
+---+--+--+

  SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
  FROM nu
  GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 | 4|
|3 | NULL |
+--+--+

As you can see, id=3 has a row with type=4, but it isn't found.

You could do this:

  SELECT id, IF(SUM(type=4)>0, 4, NULL) AS type FROM nu GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 |4 |
|3 |4 |
+--+--+

but it's hard to see how that's better than your previous, simpler 
suggestion


  SELECT DISTINCT id FROM nu WHERE type = 4;

Michael


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on sql statement (not MySQL specifik)

[Rhino]

You're absolutely right that I'd need some good luck for this query to work 
for every possible data value that the table could continue.


I realized the combination of 'group by id' and 'select id, type' was not 
very good SQL - 'select id, ' would be a much more standard 
construction to go with 'group by id' - as I developed that query. But I was 
too lazy to dig through the manual to find out exactly what MySQL would do 
with that query; it worked fine for the data given. But you're right, I 
should have at least warned that this was dubious SQL before posting it. The 
original poster could easily have though that this was actually good SQL 
when it isn't.


Again, cleaning up the data (assuming it is messed up!) should be the first 
priority and any query would just be a bandaid until that is done. The query 
would probably be a lot easier if the data was clean to start with.


In any case, thanks for keeping me honest.

--
Rhino

- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>

To: "Rhino" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 2:26 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Rhino wrote:
I don't really understand _why_ you want to do this but here is a query 
that gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave 
it alone, otherwise display null.


--
Rhino


Unfortunately, that won't work unless you are very lucky.  You aren't 
grouping by type, and CASE is not an aggregate function.  Mysql will use 
the value for type from the first row it finds for each id in the CASE 
statement.  The following illustrate the problem:


  DROP TABLE nu;
  CREATE TABLE nu (recno INT, id INT, type INT);
  INSERT INTO nu VALUES
  (1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);

  SELECT * FROM nu;
+---+--+--+
| recno | id   | type |
+---+--+--+
| 1 |1 | NULL |
| 2 |2 |4 |
| 3 |2 |6 |
| 4 |3 |5 |
| 5 |3 |4 |
| 6 |3 |3 |
+---+--+--+

  SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
  FROM nu
  GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 | 4|
|3 | NULL |
+--+--+

As you can see, id=3 has a row with type=4, but it isn't found.

You could do this:

  SELECT id, IF(SUM(type=4)>0, 4, NULL) AS type FROM nu GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 |4 |
|3 |4 |
+--+--+

but it's hard to see how that's better than your previous, simpler 
suggestion


  SELECT DISTINCT id FROM nu WHERE type = 4;

Michael


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on sql statement (not MySQL specifik)

[Michael Stassen]

Rhino wrote:
I don't really understand _why_ you want to do this but here is a query 
that gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave 
it alone, otherwise display null.


--
Rhino


Unfortunately, that won't work unless you are very lucky.  You aren't grouping 
by type, and CASE is not an aggregate function.  Mysql will use the value for 
type from the first row it finds for each id in the CASE statement.  The 
following illustrate the problem:


  DROP TABLE nu;
  CREATE TABLE nu (recno INT, id INT, type INT);
  INSERT INTO nu VALUES
  (1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);

  SELECT * FROM nu;
+---+--+--+
| recno | id   | type |
+---+--+--+
| 1 |1 | NULL |
| 2 |2 |4 |
| 3 |2 |6 |
| 4 |3 |5 |
| 5 |3 |4 |
| 6 |3 |3 |
+---+--+--+

  SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
  FROM nu
  GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 | 4|
|3 | NULL |
+--+--+

As you can see, id=3 has a row with type=4, but it isn't found.

You could do this:

  SELECT id, IF(SUM(type=4)>0, 4, NULL) AS type FROM nu GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 |4 |
|3 |4 |
+--+--+

but it's hard to see how that's better than your previous, simpler suggestion

  SELECT DISTINCT id FROM nu WHERE type = 4;

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I agree with Michael. If your data is actually messed up, the right solution 
is to fix the data and prevent it from getting messed up again FIRST. 
Writing SQL that compensates for the messed up data is often possible but 
doesn't fix the problem; you'll still need to fix the data and the sooner 
you do that, the better.


Fixing the data has a major side-benefit too: it usually makes your queries 
a lot easier and more logical. But, as Michael has said, you haven't 
explained very much about the 'Big Picture' of your situation so maybe there 
is nothing wrong with your data at all. In that case, the query I just 
posted should meet your needs.


If you _do_ have a data problem, Michael's suggestions make a lot of sense 
but if you don't understand them or if your situation is actually different 
than Michael assumes, please post again and people will try to help you.


--
Rhino


- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>

To: "Søren Merser" <[EMAIL PROTECTED]>
Cc: 
Sent: Sunday, March 05, 2006 1:13 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Søren Merser wrote:

Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is 
preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren


You appear to have several, separate issues.  First, your table has 
duplicate entries, which you don't want.  If you do not want duplicate 
entries, you should not allow them.  That is, you need a UNIQUE constraint 
on the id column.  You won't be able to add one, however, until you remove 
the existing duplicates.


To remove duplicates, you first have to decide which to keep and which to 
toss. In your example, you always keep the row with the lowest recno, but 
your description implies that when one of the duplicates has type = 4, you 
want to keep that one, regardless of recno.  Assuming that to be true, you 
need something like:


  DELETE t1
  FROM yourtable t1 JOIN yourtable t2 ON t1.id = t2.id
  WHERE (t1.recno > t2.recno AND t1.type !=4)
 OR (t1.recno < t2.recno AND t2.type = 4);

(The exact syntax depends on your version of mysql.  See the manual for 
details <http://dev.mysql.com/doc/refman/4.1/en/delete.html>.)


Now add a UNIQUE constraint on id so this won't ever happen again:

  ALTER TABLE yourtable ADD UNIQUE (id);

In your example, you have renumbered recno.  This is almost always a bad 
idea, but you can do it with:


  SET @i = 0;
  UPDATE yourtable SET recno = (@i := @i + 1);

Finally, now that you've fixed the table, the requested update is simple:

  UPDATE yourtable SET type = NULL WHERE type != 4;

Having said all that, I'm skeptical this is the best solution.  Perhaps it 
is just lack of imagination on my part, but I'm having trouble seeing why 
you would want to do things this way.  Having a column which should be 
unique, but isn't, and wanting to renumber your primary key column are 
both red flags.  I also find it strange that you seem to want to find the 
unique ids with type = 4, but you are changing every other type to NULL in 
the process.  Why not just select what you want, as Rhino suggested?


  SELECT DISTINCT id FROM yourtable WHERE type = 4;

Perhaps this all makes sense given the context. (You haven't told us 
much).  On the other hand, if you carefully describe what you are trying 
to accomplish, one of the many experts on the list may well be able to 
supply you with a better way.


Michael

--
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 incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I don't really understand _why_ you want to do this but here is a query that 
gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave it 
alone, otherwise display null.


--
Rhino


- Original Message - 
From: "Søren Merser" <[EMAIL PROTECTED]>

To: ; "Rhino" <[EMAIL PROTECTED]>
Sent: Sunday, March 05, 2006 11:46 AM
Subject: Re: Help on sql statement (not MySQL specifik)



Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is 
preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren




TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9        | 6|3<-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 
4,
selects all the unique id's with TYPE = 4 when appropriate otherwise 
NULL


like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still not 
clear on what you want but maybe this will help until you can clarify the 
question.


You use WHERE to limit your result set to only specific rows of the 
original table. Therefore, if you want only rows where the type is 4, you 
say:


Select 
from 
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID column 
in the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the 
result set and you just want to know the unique values of ID that had a 
type of 4, you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. 
There is only one row in your sample table that has a type of 4 so 
DISTINCT isn't going to do anything for you; you'll get the same result 
with or without DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want to 
update the data in your table permanently so that a null isn't null any 
longer (or a non-null value is null)? Or do you want to _display_ a null 
where something isn't null? I don't understand what your "DROP" and 
"LEAVE AS IS" remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


--
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 incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on sql statement (not MySQL specifik)

[Michael Stassen]

Søren Merser wrote:

Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one 
is preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren


You appear to have several, separate issues.  First, your table has duplicate 
entries, which you don't want.  If you do not want duplicate entries, you should 
not allow them.  That is, you need a UNIQUE constraint on the id column.  You 
won't be able to add one, however, until you remove the existing duplicates.


To remove duplicates, you first have to decide which to keep and which to toss. 
 In your example, you always keep the row with the lowest recno, but your 
description implies that when one of the duplicates has type = 4, you want to 
keep that one, regardless of recno.  Assuming that to be true, you need 
something like:


  DELETE t1
  FROM yourtable t1 JOIN yourtable t2 ON t1.id = t2.id
  WHERE (t1.recno > t2.recno AND t1.type !=4)
 OR (t1.recno < t2.recno AND t2.type = 4);

(The exact syntax depends on your version of mysql.  See the manual for details 
.)


Now add a UNIQUE constraint on id so this won't ever happen again:

  ALTER TABLE yourtable ADD UNIQUE (id);

In your example, you have renumbered recno.  This is almost always a bad idea, 
but you can do it with:


  SET @i = 0;
  UPDATE yourtable SET recno = (@i := @i + 1);

Finally, now that you've fixed the table, the requested update is simple:

  UPDATE yourtable SET type = NULL WHERE type != 4;

Having said all that, I'm skeptical this is the best solution.  Perhaps it is 
just lack of imagination on my part, but I'm having trouble seeing why you would 
want to do things this way.  Having a column which should be unique, but isn't, 
and wanting to renumber your primary key column are both red flags.  I also find 
it strange that you seem to want to find the unique ids with type = 4, but you 
are changing every other type to NULL in the process.  Why not just select what 
you want, as Rhino suggested?


  SELECT DISTINCT id FROM yourtable WHERE type = 4;

Perhaps this all makes sense given the context. (You haven't told us much).  On 
the other hand, if you carefully describe what you are trying to accomplish, one 
of the many experts on the list may well be able to supply you with a better way.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on sql statement (not MySQL specifik)

[Søren Merser]

Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with TYPE 
set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is 
preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren




TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3        <-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 
4,

selects all the unique id's with TYPE = 4 when appropriate otherwise NULL

like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still not 
clear on what you want but maybe this will help until you can clarify the 
question.


You use WHERE to limit your result set to only specific rows of the 
original table. Therefore, if you want only rows where the type is 4, you 
say:


Select 
from 
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID column 
in the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the 
result set and you just want to know the unique values of ID that had a 
type of 4, you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. There 
is only one row in your sample table that has a type of 4 so DISTINCT 
isn't going to do anything for you; you'll get the same result with or 
without DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want to 
update the data in your table permanently so that a null isn't null any 
longer (or a non-null value is null)? Or do you want to _display_ a null 
where something isn't null? I don't understand what your "DROP" and "LEAVE 
AS IS" remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


--
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: Help on sql statement (not MySQL specifik)

[Ronald J. Yacketta]

I think he wants to update rows where != 4 to null

that is, update TYPE setting TYPE to null where TYPE  != 4

-Ron
Rhino wrote:



- Original Message - From: "Søren Merser" <[EMAIL PROTECTED]>
To: 
Sent: Sunday, March 05, 2006 9:12 AM
Subject: Help on sql statement (not MySQL specifik)



Hi

Could someone please help me out here?

TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3        <-DROP

What I neede is a SQL statement that for a given value of TYPE, lets 
say 4,
selects all the unique id's with TYPE = 4 when appropriate otherwise 
NULL


like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still 
not clear on what you want but maybe this will help until you can 
clarify the question.


You use WHERE to limit your result set to only specific rows of the 
original table. Therefore, if you want only rows where the type is 4, 
you say:


Select 
from 
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID 
column in the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the 
result set and you just want to know the unique values of ID that had 
a type of 4, you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. 
There is only one row in your sample table that has a type of 4 so 
DISTINCT isn't going to do anything for you; you'll get the same 
result with or without DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want 
to update the data in your table permanently so that a null isn't null 
any longer (or a non-null value is null)? Or do you want to _display_ 
a null where something isn't null? I don't understand what your "DROP" 
and "LEAVE AS IS" remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on sql statement (not MySQL specifik)

[Rhino]

- Original Message - 
From: "Søren Merser" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, March 05, 2006 9:12 AM
Subject: Help on sql statement (not MySQL specifik)



Hi

Could someone please help me out here?

TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3        <-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 
4,

selects all the unique id's with TYPE = 4 when appropriate otherwise NULL

like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still not 
clear on what you want but maybe this will help until you can clarify the 
question.


You use WHERE to limit your result set to only specific rows of the original 
table. Therefore, if you want only rows where the type is 4, you say:


Select 
from 
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID column in 
the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the result 
set and you just want to know the unique values of ID that had a type of 4, 
you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. There 
is only one row in your sample table that has a type of 4 so DISTINCT isn't 
going to do anything for you; you'll get the same result with or without 
DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want to 
update the data in your table permanently so that a null isn't null any 
longer (or a non-null value is null)? Or do you want to _display_ a null 
where something isn't null? I don't understand what your "DROP" and "LEAVE 
AS IS" remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help on sql statement (not MySQL specifik)

[Søren Merser]

Hi

Could someone please help me out here?

TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3<-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 4,
selects all the unique id's with TYPE = 4 when appropriate otherwise NULL

like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list
Regards Soren


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Statement Help

[Charles Walmsley]

Dear Jesse

Could I please reinforce what Shawn is implying and suggest you look again
at your table structure?  All my experience over many years with DB design
(admittedly not with MySql) suggest that you make have started to dig a
little hole but it could easily become a very large one.

With best wishes

Ch
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of [EMAIL PROTECTED]
Sent: 14 December 2005 19:22
To: Jesse
Cc: mysql@lists.mysql.com
Subject: Re: SQL Statement Help

 
"Jesse" <[EMAIL PROTECTED]> wrote on 12/14/2005 02:03:22 PM:

> Hello,
> I need help building a SQL Statement.  I'm currently using MySQL 
> 5.something.  This one is kind of strange, and I can't seem to figure 
out 
> how to do it.
> 
> I have a table named Participants. Each Participant is allowed to bring 
up 
> to 5 Guests.  Instead of putting the names in a separate table (which 
would 
> take more coding), I just added 5 Guest fields named Guest1-Guest5 (Yes, 
we 
> could argue about normalization here, but I'd rather not).  When 
displaying 
> this information in a grid, however, I would like to display the 
> Participants name, and the number of guests they're bringing.  Example:
> 
> Joe Blow        5
> Nancy Vila2
> Henry Morgan1
> 
> How do I build a SQL Statement that will somehow give a count if a Guest 

> field has something in it?  I could build a case statement something 
like 
> this:
> CASE WHEN Guest1 IS NOT NULL THEN 1 WHEN Guest2 IS NOT NULL THEN 2 ... 
END
> 
> However, I know how users are. Someone is gong to put a value in the 
Guest1 
> field, skip Guest2, and put something in Guest3.  So, I need a better 
> solution.
> 
> I am using ASP.Net to display the data, and I'm using a DataGrid.  They 
tend 
> to be a bit more difficult to figure out how to do this sort of thing 
with, 
> or I would probably have just coded it.  If I can find a SQL Solution, 
that 
> would be best here.
> 
> Anyone have any ideas?
> 
> Thanks,
> Jesse 
> 
> 
Yes, I need to gently chide you about breaking normalization. You have 
traded a little bit of coding used to validate your data and populate the 
table with a whole bunch of coding to maintain and use it. The simple, 
robust query (from the normalized solution) should have looked something 
like this:

SELECT p.name, count(g.id)
FROM participant p
LEFT JOIN guest g
 ON p.id = g.participant_id
GROUP BY p.name;

Instead you have to do conditional math across all 5 guest columns to get 
your data. If sometime in the future, someone decides that there should be 
up to 20 guests per participant, your table suddenly gets 4 times wider 
and this query (and every other query that uses this table, including your 
INSERT statements) becomes 4 times nastier. Doing it the other way would 
require no table changes, little to no query changes, and little to no 
reporting code modification if you decided to allow more than 5 guests per 
participant.

Anyway, to get your guest counts, try this (assuming that any non-NULL 
value is a valid guest):
SELECT name, 
5-isnull(guest1)-isnull(guest2)-isnull(guest3)-isnull(guest4)-isnull(guest5)

as guests
FROM participant;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Statement Help

[SGreen]

"Jesse" <[EMAIL PROTECTED]> wrote on 12/14/2005 02:03:22 PM:

> Hello,
> I need help building a SQL Statement.  I'm currently using MySQL 
> 5.something.  This one is kind of strange, and I can't seem to figure 
out 
> how to do it.
> 
> I have a table named Participants. Each Participant is allowed to bring 
up 
> to 5 Guests.  Instead of putting the names in a separate table (which 
would 
> take more coding), I just added 5 Guest fields named Guest1-Guest5 (Yes, 
we 
> could argue about normalization here, but I'd rather not).  When 
displaying 
> this information in a grid, however, I would like to display the 
> Participants name, and the number of guests they're bringing.  Example:
> 
> Joe Blow        5
> Nancy Vila2
> Henry Morgan1
> 
> How do I build a SQL Statement that will somehow give a count if a Guest 

> field has something in it?  I could build a case statement something 
like 
> this:
> CASE WHEN Guest1 IS NOT NULL THEN 1 WHEN Guest2 IS NOT NULL THEN 2 ... 
END
> 
> However, I know how users are. Someone is gong to put a value in the 
Guest1 
> field, skip Guest2, and put something in Guest3.  So, I need a better 
> solution.
> 
> I am using ASP.Net to display the data, and I'm using a DataGrid.  They 
tend 
> to be a bit more difficult to figure out how to do this sort of thing 
with, 
> or I would probably have just coded it.  If I can find a SQL Solution, 
that 
> would be best here.
> 
> Anyone have any ideas?
> 
> Thanks,
> Jesse 
> 
> 
Yes, I need to gently chide you about breaking normalization. You have 
traded a little bit of coding used to validate your data and populate the 
table with a whole bunch of coding to maintain and use it. The simple, 
robust query (from the normalized solution) should have looked something 
like this:

SELECT p.name, count(g.id)
FROM participant p
LEFT JOIN guest g
 ON p.id = g.participant_id
GROUP BY p.name;

Instead you have to do conditional math across all 5 guest columns to get 
your data. If sometime in the future, someone decides that there should be 
up to 20 guests per participant, your table suddenly gets 4 times wider 
and this query (and every other query that uses this table, including your 
INSERT statements) becomes 4 times nastier. Doing it the other way would 
require no table changes, little to no query changes, and little to no 
reporting code modification if you decided to allow more than 5 guests per 
participant.

Anyway, to get your guest counts, try this (assuming that any non-NULL 
value is a valid guest):
SELECT name, 
5-isnull(guest1)-isnull(guest2)-isnull(guest3)-isnull(guest4)-isnull(guest5) 
as guests
FROM participant;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: SQL Statement Help

[Martijn Tonies]

> Hello,
> I need help building a SQL Statement.  I'm currently using MySQL
> 5.something.  This one is kind of strange, and I can't seem to figure out
> how to do it.
>
> I have a table named Participants. Each Participant is allowed to bring up
> to 5 Guests.  Instead of putting the names in a separate table (which
would
> take more coding), I just added 5 Guest fields named Guest1-Guest5 (Yes,
we
> could argue about normalization here, but I'd rather not).  When
displaying
> this information in a grid, however, I would like to display the
> Participants name, and the number of guests they're bringing.  Example:
>
> Joe Blow5
> Nancy Vila    2
> Henry Morgan1
>
> How do I build a SQL Statement that will somehow give a count if a Guest
> field has something in it?  I could build a case statement something like
> this:
> CASE WHEN Guest1 IS NOT NULL THEN 1 WHEN Guest2 IS NOT NULL THEN 2 ... END
>
> However, I know how users are. Someone is gong to put a value in the
Guest1
> field, skip Guest2, and put something in Guest3.  So, I need a better
> solution.

LOL ... guess why normalization is your friend ... a simple "count" would
do the trick.

> Anyone have any ideas?

Can you use a Stored Procedure or Function to do your counting? That would
help, right?


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SQL Statement Help

[Jesse]

Hello,
I need help building a SQL Statement.  I'm currently using MySQL 
5.something.  This one is kind of strange, and I can't seem to figure out 
how to do it.


I have a table named Participants. Each Participant is allowed to bring up 
to 5 Guests.  Instead of putting the names in a separate table (which would 
take more coding), I just added 5 Guest fields named Guest1-Guest5 (Yes, we 
could argue about normalization here, but I'd rather not).  When displaying 
this information in a grid, however, I would like to display the 
Participants name, and the number of guests they're bringing.  Example:


Joe Blow5
Nancy Vila2
Henry Morgan1

How do I build a SQL Statement that will somehow give a count if a Guest 
field has something in it?  I could build a case statement something like 
this:

CASE WHEN Guest1 IS NOT NULL THEN 1 WHEN Guest2 IS NOT NULL THEN 2 ... END

However, I know how users are. Someone is gong to put a value in the Guest1 
field, skip Guest2, and put something in Guest3.  So, I need a better 
solution.


I am using ASP.Net to display the data, and I'm using a DataGrid.  They tend 
to be a bit more difficult to figure out how to do this sort of thing with, 
or I would probably have just coded it.  If I can find a SQL Solution, that 
would be best here.


Anyone have any ideas?

Thanks,
Jesse 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: where is the mistake in this SQL statement?

[Joerg Bruehe]

Hi Peter, all!


Peter Matulis wrote:

--- Joerg Bruehe <[EMAIL PROTECTED]> wrote:


[[...]]

CREATE TABLE users (
 id PRIMARY KEY,
 priority integer NOT NULL DEFAULT '7',
 policy_id  integer unsigned NOT NULL DEFAULT '1',


Even though this may work, it is wrong IMNSHO:
You set character strings as default values for numeric columns!
Your strings consist of digits only, so they can (and will) be
converted 
to numbers, but IMO you should not make use of that.



I don't understand.  I have integer there.  Where is character strings?


The column type is integer, right. But the default values '7' and '1' 
are strings, by their quotes; only the unquoted numbers 7 and 1 would be 
integer.


I know that MySQL silently converts, if possible.

I was not aware of the quoting that happens in the "SHOW CREATE TABLE" 
output which Harald then showed - sorry.
So it is quite possible that Peter gave plain integer default values but 
then used an output in his mail which had got the quotes added. Please 
accept my apologies for this.


Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: where is the mistake in this SQL statement?

[Peter Matulis]

--- Joerg Bruehe <[EMAIL PROTECTED]> wrote:

> Sujay Koduri wrote:
> > You havent specified the datatype for the column 'id'.
> 
> Right, this should be fatal.
> But I also take issue with other parts:
> 

> > 
> > CREATE TABLE users (
> >   id PRIMARY KEY,
> >   priority integer NOT NULL DEFAULT '7',
> >   policy_id  integer unsigned NOT NULL DEFAULT '1',
> 
> Even though this may work, it is wrong IMNSHO:
> You set character strings as default values for numeric columns!
> Your strings consist of digits only, so they can (and will) be
> converted 
> to numbers, but IMO you should not make use of that.

I don't understand.  I have integer there.  Where is character strings?






__ 
Find your next car at http://autos.yahoo.ca

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: where is the mistake in this SQL statement?

[Jasper Bryant-Greene]

Harald Fuchs wrote:

In article <[EMAIL PROTECTED]>,
Joerg Bruehe <[EMAIL PROTECTED]> writes:


CREATE TABLE users (
id PRIMARY KEY,
priority integer NOT NULL DEFAULT '7',
policy_id  integer unsigned NOT NULL DEFAULT '1',



Even though this may work, it is wrong IMNSHO:
You set character strings as default values for numeric columns!
Your strings consist of digits only, so they can (and will) be
converted to numbers, but IMO you should not make use of that.


You're right, but MySQL thinks otherwise:
[snip]


MySQL is very weakly typed. This behaviour is by design.

Jasper

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: where is the mistake in this SQL statement?

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Joerg Bruehe <[EMAIL PROTECTED]> writes:

>> CREATE TABLE users (
>> id PRIMARY KEY,
>> priority integer NOT NULL DEFAULT '7',
>> policy_id  integer unsigned NOT NULL DEFAULT '1',

> Even though this may work, it is wrong IMNSHO:
> You set character strings as default values for numeric columns!
> Your strings consist of digits only, so they can (and will) be
> converted to numbers, but IMO you should not make use of that.

You're right, but MySQL thinks otherwise:

  CREATE TABLE t1 (
val INT NOT NULL DEFAULT 42
  );

  SHOW CREATE TABLE t1;

returns

  CREATE TABLE `t1` (
`val` int(11) NOT NULL default '42'
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci

on 5.0.15.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: where is the mistake in this SQL statement?

[Joerg Bruehe]

Hi all, Peter!


Sujay Koduri wrote:

You havent specified the datatype for the column 'id'.


Right, this should be fatal.
But I also take issue with other parts:



[[...]]

-Original Message-
From: Peter Matulis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 15, 2005 10:57 AM

[[...]]

Hello, I am using MySQL 4.0.24 on OpenBSD 3.8.

I am having difficulty creating a SQL schema.  My attempts contain lines
like:

CREATE TABLE users (
  id PRIMARY KEY,
  priority integer NOT NULL DEFAULT '7',
  policy_id  integer unsigned NOT NULL DEFAULT '1',


Even though this may work, it is wrong IMNSHO:
You set character strings as default values for numeric columns!
Your strings consist of digits only, so they can (and will) be converted 
to numbers, but IMO you should not make use of that.



  [[...]]



Regards,
Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: where is the mistake in this SQL statement?

[Sujay Koduri]

You havent specified the datatype for the column 'id'.

sujay 

-Original Message-
From: Peter Matulis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 15, 2005 10:57 AM
To: mysql@lists.mysql.com
Subject: where is the mistake in this SQL statement?

{ this is a resend, forgot subject in original posting }

Hello, I am using MySQL 4.0.24 on OpenBSD 3.8.

I am having difficulty creating a SQL schema.  My attempts contain lines
like:

CREATE TABLE users (
  id PRIMARY KEY,
  priority integer NOT NULL DEFAULT '7',
  policy_id  integer unsigned NOT NULL DEFAULT '1',
  email varchar(255) NOT NULL,
  fullname varchar(255) DEFAULT NULL,
  local char(1),
  KEY email (email)
);

Resulting in this error:

ERROR 1064 at line 1: You have an error in your SQL syntax.  Check the
manual that corresponds to your MySQL server version for the right syntax to
use near 'PRIMARY KEY,  
  priority   integer  NOT NULL DEFAULT '7', 

I think there is an incompatibility between my server version and this
statement but I can't figure out what it is.  What should be changed near
PRIMARY KEY?  This is for Spamassassin and amavisd-new.






__
Find your next car at http://autos.yahoo.ca

--
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]

where is the mistake in this SQL statement?

[Peter Matulis]

{ this is a resend, forgot subject in original posting }

Hello, I am using MySQL 4.0.24 on OpenBSD 3.8.

I am having difficulty creating a SQL schema.  My attempts contain
lines like:

CREATE TABLE users (
  id PRIMARY KEY,
  priority integer NOT NULL DEFAULT '7',
  policy_id  integer unsigned NOT NULL DEFAULT '1',
  email varchar(255) NOT NULL,
  fullname varchar(255) DEFAULT NULL,
  local char(1),
  KEY email (email)
);

Resulting in this error:

ERROR 1064 at line 1: You have an error in your SQL syntax.  Check
the manual that corresponds to your MySQL server version for the
right syntax to use near 'PRIMARY KEY,  
  priority   integer  NOT NULL DEFAULT '7', 

I think there is an incompatibility between my server version and
this statement but I can't figure out what it is.  What should be
changed near PRIMARY KEY?  This is for Spamassassin and amavisd-new.






__ 
Find your next car at http://autos.yahoo.ca

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Statement Conversion

[Gordon Bruce]

If your MySQL server is a *nix system than table names are case
sensitive.

SELECT A.*, 
   CASE CounselorOnly 
 WHEN 1 THEN 'Yes' 
 WHEN 0 THEN 'No' 
   END AS CO
FROM Activities A 
ORDER BY Activity 

I also just noticed, remove the "CO =" and add "AS CO" following the END
of the case statement.

-Original Message-
From: Jesse Castleberry [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 04, 2005 3:54 PM
To: MySQL List
Subject: SQL Statement Conversion

I'm converting an application from SQL Server to MySQL.  I have the
following query that I need to convert:

SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No'
END
FROM Activities a ORDER BY Activity

What is the proper syntax for this in MySQL?

Thanks,
Jesse


-- 
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]

SQL Statement Conversion

[Jesse Castleberry]

I'm converting an application from SQL Server to MySQL.  I have the
following query that I need to convert:

SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END
FROM Activities a ORDER BY Activity

What is the proper syntax for this in MySQL?

Thanks,
Jesse


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Thank you ... Help on writing a sql statement

[Imran]

Hi Shawn:

Just wanted to publicly thank you for the time you took to help me. I think 
that it is important that guys like yourself who take time out from your busy 
work be appreciated when their solution made a huge difference.

I was developing an ASP application along with Crystal reports for a customer 
which was reporting from million of rows of data and my original approach 
resulted in very poor performance .. reports being VERY sluggish (45 mins to 
run some). I followed your concept and the same report that took 45 mins to run 
took 40 seconds

Unfortunately I could not implement it using MySql because the current version 
of MySql ODBC (3.51) did not expose the stored procedures to CR and I could not 
find an OleDb provider that will work for MySql. However, I ended up using Sql 
Server but I followed you suggestion and gained tremendous performance 
improvements.

Keep up the good work.

Best regards
Imran

 Solution 
---

Let me see if I can translate what you want in a query into regular language. I 
think you would like to see, grouped by date, customer, and product, the total 
cost and total sales for each (date,customer,product) triple along with each 
product's description ,code, and the customer's number and name. All of that 
will be limited to activity on or before midnight of a certain date. 

If I rephrased that correctly, here is how I would build your query. Step 1 is 
to perform the (date,customer,product) summations. By minimizing the number of 
rows, columns, and/or tables we need to summarize against, we improve 
performance. So I do this part of the analysis before I join in the other 
tables. 

Note: Date, time, and datetime literals are represented by single-quoted 
strings. You do not need the DATE() function to create a date literal. 


CREATE TEMPORARY TABLE tmpTotals ( 
key(CustNo) 
, key(ProdNo) 
) 
SELECT PostingDate 
, CustNo 
, ProdNo 
, sum(Cost) as costs 
, sum(Sales) as sales 
FROM salesmaster 
WHERE PostingDate <= '2005-09-01 00:00:00' 
GROUP BY PostingDate, CustNo, ProdNo; 

Step 2: collect the rest of the information for the report. 
SELECT CustNo 
, c.Name as custname 
, ProdNo 
, p.Name as prodname 
, costs 
, sales 
, PostingDate 
FROM tmpTotals tt 
LEFT JOIN customerintermediate c 
ON c.CustNo = tt.CustNo 
LEFT JOIN productintermediate p 
ON p.ProdNo = tt.ProdNo 
ORDER BY ... your choice... ; 

Step 3: The database is not your momma. Always clean up after yourself. 

DROP TEMPORARY TABLE tmpTotals; 

And you are done! The only trick to doing a sequence of statements in a row 
(like this) is that they all have to go through the same connection. As long as 
you do not close and re-open the connection between statements, any temp tables 
or @-variables you create or define remain in existence for the life of the 
connection. Depending on your connection library, you might be able to execute 
all three statements from a single request. Most likely, you will need to send 
them in one-at-a-time. 

Does this help you to organize your thoughts? 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

Re: Help needed for SQL statement

[Alvaro Cobo]

Is this what you are looking for:

SELECT clone_ids, COUNT(DISTINCT(gene_ids)) as count_genes
FROM table_name
GROUP BY clone_ids

or

SELECT clone_ids, COUNT(gene_ids) as count_genes
FROM table_name
GROUP BY clone_ids

Hope this helps,

Alvaro
- Original Message -
From: "Xiaobo Chen" <[EMAIL PROTECTED]>
To: 
Sent: Monday, October 24, 2005 11:43 PM
Subject: Help needed for SQL statement


> Hi,
>
> I have such a situation:
>
> There is a table with gene_ids and clone_ids. Each gene only resides on a
> single clone and each clone may contain multiple genes. How do would I
> find how many genes are on each and every clone?
>
> Thanks in advance.
>
> X.Chen
>
>
> --
> 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]

Help needed for SQL statement

[Xiaobo Chen]

Hi,

I have such a situation:

There is a table with gene_ids and clone_ids. Each gene only resides on a
single clone and each clone may contain multiple genes. How do would I
find how many genes are on each and every clone?

Thanks in advance.

X.Chen


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL statement help

[SGreen]

"C.F. Scheidecker Antunes" <[EMAIL PROTECTED]> wrote on 10/16/2005 
01:58:56 PM:

> I actually forgot something. I need a two statements into one. The 
> reason is that table_two has multiple values, so I need the minimal ID 
> from table 2.Thanks.
> 
> Maybe I am working too much here and tired to see the right statement.
> 
> J.R. Bullington wrote:
> 
> >I am not the smartest on the list, but I think this would do it for 
you.
> >
> >UPDATE table_one t1, table_two t2 SET t1.ID = t2.ID WHERE t2.num = 
t1.num
> >AND t1.ID = 0
> >
> >J.R.
> >
> >-Original Message-
> >From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] 
> >Sent: Sunday, October 16, 2005 1:45 PM
> >To: mysql@lists.mysql.com
> >Subject: SQL statement help
> >Importance: High
> >
> >Hello All,
> >
> >I have two MySQL 4.1 tables. The first table has an ID field which is 
set to
> >zero. The second one has ID fields and filenames.
> >
> >I need a single SQL statement to set the ID from table 1 to the ID from
> >table 2 ONLY IF the ID on one is zero.
> >That is, if the ID on table one is not set (still equal to zero), grab 
the
> >ID from table2 where the num of table2 is equal to table1.
> >
> >table one
> >- Title
> >- num
> >- ID (set to zero initially)
> >
> >table two
> >- num
> >- ID
> >- filename
> >
> >SET table1.ID =  if table1.ID EQUAL to zero.
> >Set the ID on table1 from the table 2 where the num on table 2 equals 
the
> >num on table1 only if the ID on table 1 is zero.
> >
> >Thank you in advance,
> >
> >C.F.


Once you identify that you want one row identified by a min or max value 
from a set of matching rows, you can use any of the patterns for 
group-wize maximum. Start here:
http://dev.mysql.com/doc/refman/4.1/en/examples.html
and look especially here:
 
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
and here
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-row.html

for a pattern you can use.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: SQL statement help

[Terence]

Turn off your auto commit (if using innodb) and run the queries. See how 
many rows it updates and compare them. If it matches commit it. Else 
rollback.


C.F. Scheidecker Antunes wrote:

Another thing is that table2 might not have a match.

C.F. Scheidecker Antunes wrote:

I actually forgot something. I need a two statements into one. The 
reason is that table_two has multiple values, so I need the minimal ID 
from table 2.Thanks.


Maybe I am working too much here and tired to see the right statement.

J.R. Bullington wrote:


I am not the smartest on the list, but I think this would do it for you.

UPDATE table_one t1, table_two t2 SET t1.ID = t2.ID WHERE t2.num = 
t1.num

AND t1.ID = 0

J.R.

-Original Message-
From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: 
Sunday, October 16, 2005 1:45 PM

To: mysql@lists.mysql.com
Subject: SQL statement help
Importance: High

Hello All,

I have two MySQL 4.1 tables. The first table has an ID field which is 
set to

zero. The second one has ID fields and filenames.

I need a single SQL statement to set the ID from table 1 to the ID from
table 2 ONLY IF the ID on one is zero.
That is, if the ID on table one is not set (still equal to zero), 
grab the

ID from table2 where the num of table2 is equal to table1.

table one
- Title
- num
- ID (set to zero initially)

table two
- num
- ID
- filename

SET table1.ID =  if table1.ID EQUAL to zero.
Set the ID on table1 from the table 2 where the num on table 2 equals 
the

num on table1 only if the ID on table 1 is zero.

Thank you in advance,

C.F.

--
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: SQL statement help

[C.F. Scheidecker Antunes]

Another thing is that table2 might not have a match.

C.F. Scheidecker Antunes wrote:

I actually forgot something. I need a two statements into one. The 
reason is that table_two has multiple values, so I need the minimal ID 
from table 2.Thanks.


Maybe I am working too much here and tired to see the right statement.

J.R. Bullington wrote:


I am not the smartest on the list, but I think this would do it for you.

UPDATE table_one t1, table_two t2 SET t1.ID = t2.ID WHERE t2.num = 
t1.num

AND t1.ID = 0

J.R.

-Original Message-
From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: 
Sunday, October 16, 2005 1:45 PM

To: mysql@lists.mysql.com
Subject: SQL statement help
Importance: High

Hello All,

I have two MySQL 4.1 tables. The first table has an ID field which is 
set to

zero. The second one has ID fields and filenames.

I need a single SQL statement to set the ID from table 1 to the ID from
table 2 ONLY IF the ID on one is zero.
That is, if the ID on table one is not set (still equal to zero), 
grab the

ID from table2 where the num of table2 is equal to table1.

table one
- Title
- num
- ID (set to zero initially)

table two
- num
- ID
- filename

SET table1.ID =  if table1.ID EQUAL to zero.
Set the ID on table1 from the table 2 where the num on table 2 equals 
the

num on table1 only if the ID on table 1 is zero.

Thank you in advance,

C.F.

--
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: SQL statement help

[C.F. Scheidecker Antunes]

I actually forgot something. I need a two statements into one. The 
reason is that table_two has multiple values, so I need the minimal ID 
from table 2.Thanks.


Maybe I am working too much here and tired to see the right statement.

J.R. Bullington wrote:


I am not the smartest on the list, but I think this would do it for you.

UPDATE table_one t1, table_two t2 SET t1.ID = t2.ID WHERE t2.num = t1.num
AND t1.ID = 0

J.R.

-Original Message-
From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] 
Sent: Sunday, October 16, 2005 1:45 PM

To: mysql@lists.mysql.com
Subject: SQL statement help
Importance: High

Hello All,

I have two MySQL 4.1 tables. The first table has an ID field which is set to
zero. The second one has ID fields and filenames.

I need a single SQL statement to set the ID from table 1 to the ID from
table 2 ONLY IF the ID on one is zero.
That is, if the ID on table one is not set (still equal to zero), grab the
ID from table2 where the num of table2 is equal to table1.

table one
- Title
- num
- ID (set to zero initially)

table two
- num
- ID
- filename

SET table1.ID =  if table1.ID EQUAL to zero.
Set the ID on table1 from the table 2 where the num on table 2 equals the
num on table1 only if the ID on table 1 is zero.

Thank you in advance,

C.F.

--
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: SQL statement help

[J.R. Bullington]

I am not the smartest on the list, but I think this would do it for you.

UPDATE table_one t1, table_two t2 SET t1.ID = t2.ID WHERE t2.num = t1.num
AND t1.ID = 0

J.R.

-Original Message-
From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] 
Sent: Sunday, October 16, 2005 1:45 PM
To: mysql@lists.mysql.com
Subject: SQL statement help
Importance: High

Hello All,

I have two MySQL 4.1 tables. The first table has an ID field which is set to
zero. The second one has ID fields and filenames.

I need a single SQL statement to set the ID from table 1 to the ID from
table 2 ONLY IF the ID on one is zero.
That is, if the ID on table one is not set (still equal to zero), grab the
ID from table2 where the num of table2 is equal to table1.

table one
- Title
- num
- ID (set to zero initially)

table two
- num
- ID
- filename

SET table1.ID =  if table1.ID EQUAL to zero.
Set the ID on table1 from the table 2 where the num on table 2 equals the
num on table1 only if the ID on table 1 is zero.

Thank you in advance,

C.F.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



smime.p7s
Description: S/MIME cryptographic signature

SQL statement help

[C.F. Scheidecker Antunes]

Hello All,

I have two MySQL 4.1 tables. The first table has an ID field which is 
set to zero. The second one has ID fields and filenames.


I need a single SQL statement to set the ID from table 1 to the ID from 
table 2 ONLY IF the ID on one is zero.
That is, if the ID on table one is not set (still equal to zero), grab 
the ID from table2 where the num of table2 is equal to table1.


table one
- Title
- num
- ID (set to zero initially)

table two
- num
- ID
- filename

SET table1.ID =  if table1.ID EQUAL to zero.
Set the ID on table1 from the table 2 where the num on table 2 equals 
the num on table1 only if the ID on table 1 is zero.


Thank you in advance,

C.F.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on writing a sql statement

[SGreen]

The CREATE TABLE ... SELECT ... command is all one command. You weren't 
supposed to break it up. Sorry if I didn't make that very clear (my 
fault!!).

You should probably be on a SCRIPT tab not a QUERY tab if you are running 
this through QueryBrowser in order to execute more than one statement at a 
time. I don't use it very often but I think that QB doesn't maintain 
connections between calls on the same tab (can't remember and can't test 
right now) and IIRC, the QUERY tabs only take one command at a time.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Imran" <[EMAIL PROTECTED]> wrote on 10/11/2005 12:00:25 PM:

> Hi Shawn:
> 
> I tried to run the sequences as you suggested in MySql Query Browser. I 
ran
> the first part with the create temp table then I ran the second select 
to
> see the result but when I tried the second SQL to get the created rows I 
get
> the message 'Table .tmpTotals doesn't exist' where =schema name.
> 
> In additon, THANK you for taking the time to clarify the confusion about
> connections .. you are a wealth of information.
> 
> best regards
> Imran
> 
> 
> 
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: "Imran" <[EMAIL PROTECTED]>
> Cc: 
> Sent: Tuesday, October 11, 2005 11:33 AM
> Subject: Re: Help on writing a sql statement
> 
> 
> > Will you have name collisions with the same temporary table names used
> > from different connections? Nope.
> >
> > Temporary tables and user-defined (@-variables) are 
connection-specific.
> > Even if the same username/password combination is used to create 
several
> > connections, each connection will have its own set of user-defined
> > variables and temporary tables. What may get confusing is if you are 
using
> > a connection pool manager (like ODBC) and you pick up a connection 
that
> > you thought was "new" but was really just "recycled".
> >
> > When you request a connection and close one and you are behind a
> > connection pool manager, the manager doesn't actually create and 
destroy
> > new connections each time. It merely loans you one it already has open 
and
> > it will open or close the connections as it sees fit. When you try to
> > close the connection, all you are really doing is telling the manager 
that
> > it is OK for some other thread/process to use it.  So long as you do 
not
> > assume a variable to have a particular value unless you set it to be
> > something (do not assume that a variable you haven't set is still 
null)
> > and so long as you destroy any temporary tables when you are through 
using
> > them, you shouldn't run into any "inheritance" problems from thread to
> > thread.
> >
> > The good thing is that in the case of ODBC (at least on Win32) you can
> > decide for each driver if you want the ODBC connection manager to pool
> > connections or not.
> >
> > Make sense?
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> > "Imran" <[EMAIL PROTECTED]> wrote on 10/11/2005 11:19:20 AM:
> >
> > > Hi Shawn:
> > >
> > > Thank you very much for your solution. It certainly helped me in
> > > understanding SQL a whole deal more.
> > >
> > > I Have one followup question as to the proposed solution, This query
> > will be
> > > used in a multi-user situation and the logon to the database will be 
the
> > > same user (a web based app) ... Since I am creating a temp table, 
will
> > the
> > > temp table creation fail for subsequent users prior to the drop i.e. 
the
> > > table will exist already exist 
> > >
> > >
> > > best regards
> > > Imran.
> > >
> > > - Original Message -
> > > From: <[EMAIL PROTECTED]>
> > > To: "Imran" <[EMAIL PROTECTED]>
> > > Cc: 
> > > Sent: Tuesday, October 11, 2005 9:49 AM
> > > Subject: Re: Help on writing a sql statement
> > >
> > >
> > > > (my response bottom-posted. See below - SG)
> > > > > - Original Message -
> > > > > From: <[EMAIL PROTECTED]>
> > > > > To: "Imran" <[EMAIL PROTECTED]>
> > > > > Cc: 
> > > > > Sent: Monday, October 10, 2005 4:17 PM
> > > > > Subject: Re: Help on writing a sql statement
> > > > >
> > > > >
> > > > > > "Imr

Re: Help on writing a sql statement

[Imran]

Hi Shawn:

I tried to run the sequences as you suggested in MySql Query Browser. I ran
the first part with the create temp table then I ran the second select to
see the result but when I tried the second SQL to get the created rows I get
the message 'Table .tmpTotals doesn't exist' where =schema name.

In additon, THANK you for taking the time to clarify the confusion about
connections .. you are a wealth of information.

best regards
Imran



- Original Message -
From: <[EMAIL PROTECTED]>
To: "Imran" <[EMAIL PROTECTED]>
Cc: 
Sent: Tuesday, October 11, 2005 11:33 AM
Subject: Re: Help on writing a sql statement


> Will you have name collisions with the same temporary table names used
> from different connections? Nope.
>
> Temporary tables and user-defined (@-variables) are connection-specific.
> Even if the same username/password combination is used to create several
> connections, each connection will have its own set of user-defined
> variables and temporary tables. What may get confusing is if you are using
> a connection pool manager (like ODBC) and you pick up a connection that
> you thought was "new" but was really just "recycled".
>
> When you request a connection and close one and you are behind a
> connection pool manager, the manager doesn't actually create and destroy
> new connections each time. It merely loans you one it already has open and
> it will open or close the connections as it sees fit. When you try to
> close the connection, all you are really doing is telling the manager that
> it is OK for some other thread/process to use it.  So long as you do not
> assume a variable to have a particular value unless you set it to be
> something (do not assume that a variable you haven't set is still null)
> and so long as you destroy any temporary tables when you are through using
> them, you shouldn't run into any "inheritance" problems from thread to
> thread.
>
> The good thing is that in the case of ODBC (at least on Win32) you can
> decide for each driver if you want the ODBC connection manager to pool
> connections or not.
>
> Make sense?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "Imran" <[EMAIL PROTECTED]> wrote on 10/11/2005 11:19:20 AM:
>
> > Hi Shawn:
> >
> > Thank you very much for your solution. It certainly helped me in
> > understanding SQL a whole deal more.
> >
> > I Have one followup question as to the proposed solution, This query
> will be
> > used in a multi-user situation and the logon to the database will be the
> > same user (a web based app) ... Since I am creating a temp table, will
> the
> > temp table creation fail for subsequent users prior to the drop i.e. the
> > table will exist already exist 
> >
> >
> > best regards
> > Imran.
> >
> > - Original Message -
> > From: <[EMAIL PROTECTED]>
> > To: "Imran" <[EMAIL PROTECTED]>
> > Cc: 
> > Sent: Tuesday, October 11, 2005 9:49 AM
> > Subject: Re: Help on writing a sql statement
> >
> >
> > > (my response bottom-posted. See below - SG)
> > > > - Original Message -
> > > > From: <[EMAIL PROTECTED]>
> > > > To: "Imran" <[EMAIL PROTECTED]>
> > > > Cc: 
> > > > Sent: Monday, October 10, 2005 4:17 PM
> > > > Subject: Re: Help on writing a sql statement
> > > >
> > > >
> > > > > "Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 03:52:21 PM:
> > > > >
> > > > > > Hi all:
> > > > > > I need some help in writing a sql statement.
> > > > > >
> > > > > > I have three tables (Sales, Cust and Product). The sales table
> > > contains
> > > > > a
> > > > > > large volume of data and I want to create a sql to group the
> sales
> > > table
> > > > > > then join the resultant to both the Cust and Prod and to have
> > > additional
> > > > > > fields selected from the Cust and Prod.
> > > > > >
> > > > > > So in effect something like (obviously syntax is wrong)
> > > > > >
> > > > > > Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as
> > > sales,
> > > > > > sm.date
> > > > > >
> > > > > > from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group
> by
> > > > > > sm.prodno, sm.custno, sm.date

Re: Help on writing a sql statement

[SGreen]

Will you have name collisions with the same temporary table names used 
from different connections? Nope. 

Temporary tables and user-defined (@-variables) are connection-specific. 
Even if the same username/password combination is used to create several 
connections, each connection will have its own set of user-defined 
variables and temporary tables. What may get confusing is if you are using 
a connection pool manager (like ODBC) and you pick up a connection that 
you thought was "new" but was really just "recycled".

When you request a connection and close one and you are behind a 
connection pool manager, the manager doesn't actually create and destroy 
new connections each time. It merely loans you one it already has open and 
it will open or close the connections as it sees fit. When you try to 
close the connection, all you are really doing is telling the manager that 
it is OK for some other thread/process to use it.  So long as you do not 
assume a variable to have a particular value unless you set it to be 
something (do not assume that a variable you haven't set is still null) 
and so long as you destroy any temporary tables when you are through using 
them, you shouldn't run into any "inheritance" problems from thread to 
thread.

The good thing is that in the case of ODBC (at least on Win32) you can 
decide for each driver if you want the ODBC connection manager to pool 
connections or not.

Make sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Imran" <[EMAIL PROTECTED]> wrote on 10/11/2005 11:19:20 AM:

> Hi Shawn:
> 
> Thank you very much for your solution. It certainly helped me in
> understanding SQL a whole deal more.
> 
> I Have one followup question as to the proposed solution, This query 
will be
> used in a multi-user situation and the logon to the database will be the
> same user (a web based app) ... Since I am creating a temp table, will 
the
> temp table creation fail for subsequent users prior to the drop i.e. the
> table will exist already exist 
> 
> 
> best regards
> Imran.
> 
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: "Imran" <[EMAIL PROTECTED]>
> Cc: 
> Sent: Tuesday, October 11, 2005 9:49 AM
> Subject: Re: Help on writing a sql statement
> 
> 
> > (my response bottom-posted. See below - SG)
> > > - Original Message -----
> > > From: <[EMAIL PROTECTED]>
> > > To: "Imran" <[EMAIL PROTECTED]>
> > > Cc: 
> > > Sent: Monday, October 10, 2005 4:17 PM
> > > Subject: Re: Help on writing a sql statement
> > >
> > >
> > > > "Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 03:52:21 PM:
> > > >
> > > > > Hi all:
> > > > > I need some help in writing a sql statement.
> > > > >
> > > > > I have three tables (Sales, Cust and Product). The sales table
> > contains
> > > > a
> > > > > large volume of data and I want to create a sql to group the 
sales
> > table
> > > > > then join the resultant to both the Cust and Prod and to have
> > additional
> > > > > fields selected from the Cust and Prod.
> > > > >
> > > > > So in effect something like (obviously syntax is wrong)
> > > > >
> > > > > Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as
> > sales,
> > > > > sm.date
> > > > >
> > > > > from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group 
by
> > > > > sm.prodno, sm.custno, sm.date ,
> > > > >
> > > > > (Select prod.desc, prod.code, cust.custno, cust.name from cust,
> > Prod)
> > > > left
> > > > > join sm.prodno = prod.code left join sm.custno=cust.custno) …
> > > > >
> > > > > Any help would be greatly appreciated.
> > > > >
> > > >
> > > > OK, you know you need a GROUP BY, that's good. You also recognized 
you
> > > > needed to JOIN a few tables together, also good. There are at 
least
> > two
> > > > ways to do what you ask. One is a fairly complex query that does 
it
> > all in
> > > > one statement (might take a long time to compute) the other is a
> > sequence
> > > > of two simpler statements. I think the two-statement solution will 
be
> > > > easier to understand and maintain so I would prefer to go over 
that.
> > > > However, in order to provide an example of either method I will 
need
> > more
> > > > info

Re: Help on writing a sql statement

[Imran]

Hi Shawn:

Thank you very much for your solution. It certainly helped me in
understanding SQL a whole deal more.

I Have one followup question as to the proposed solution, This query will be
used in a multi-user situation and the logon to the database will be the
same user (a web based app) ... Since I am creating a temp table, will the
temp table creation fail for subsequent users prior to the drop i.e. the
table will exist already exist 


best regards
Imran.

- Original Message -
From: <[EMAIL PROTECTED]>
To: "Imran" <[EMAIL PROTECTED]>
Cc: 
Sent: Tuesday, October 11, 2005 9:49 AM
Subject: Re: Help on writing a sql statement


> (my response bottom-posted. See below - SG)
> > - Original Message -
> > From: <[EMAIL PROTECTED]>
> > To: "Imran" <[EMAIL PROTECTED]>
> > Cc: 
> > Sent: Monday, October 10, 2005 4:17 PM
> > Subject: Re: Help on writing a sql statement
> >
> >
> > > "Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 03:52:21 PM:
> > >
> > > > Hi all:
> > > > I need some help in writing a sql statement.
> > > >
> > > > I have three tables (Sales, Cust and Product). The sales table
> contains
> > > a
> > > > large volume of data and I want to create a sql to group the sales
> table
> > > > then join the resultant to both the Cust and Prod and to have
> additional
> > > > fields selected from the Cust and Prod.
> > > >
> > > > So in effect something like (obviously syntax is wrong)
> > > >
> > > > Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as
> sales,
> > > > sm.date
> > > >
> > > > from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group by
> > > > sm.prodno, sm.custno, sm.date ,
> > > >
> > > > (Select prod.desc, prod.code, cust.custno, cust.name from cust,
> Prod)
> > > left
> > > > join sm.prodno = prod.code left join sm.custno=cust.custno) …
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > >
> > > OK, you know you need a GROUP BY, that's good. You also recognized you
> > > needed to JOIN a few tables together, also good. There are at least
> two
> > > ways to do what you ask. One is a fairly complex query that does it
> all in
> > > one statement (might take a long time to compute) the other is a
> sequence
> > > of two simpler statements. I think the two-statement solution will be
> > > easier to understand and maintain so I would prefer to go over that.
> > > However, in order to provide an example of either method I will need
> more
> > > information from you.
> > >
> > > From the CLI (command line client), please provide the output from
> these
> > > three commands:
> > >
> > > SHOW CREATE TABLE sales\G;
> > > SHOW CREATE TABLE cust\G;
> > > SHOW CREATE TABLE product\G;
> > >
> > > That will tell me exactly which columns live on which tables and where
> you
> > > do or do not have any indexes. Good indexes will make or break the
> > > performance of your database. You will not be exposing any data, only
> the
> > > design of the tables.
> > >
> > > Please remember to CC the list on all responses.
> > >
> > > Shawn Green
> > > Database Administrator
> > > Unimin Corporation - Spruce Pine
> > >
>
> Let me see if I can translate what you want in a query into regular
> language. I think you would like to see, grouped by date, customer, and
> product, the total cost and total sales for each (date,customer,product)
> triple along with each product's description ,code, and the customer's
> number and name. All of that will be limited to activity on or before
> midnight of a certain date.
>
> If I rephrased that correctly, here is how I would build your query. Step
> 1 is to perform the (date,customer,product) summations. By minimizing the
> number of rows, columns, and/or tables we need to summarize against, we
> improve performance. So I do this part of the analysis before I join in
> the other tables.
>
> Note: Date, time, and datetime literals are represented by single-quoted
> strings. You do not need the DATE() function to create a date literal.
>
>
> CREATE TEMPORARY TABLE tmpTotals (
> key(CustNo)
> , key(ProdNo)
> )
> SELECT PostingDate
> , CustNo
> , ProdNo
> , sum(Cost) as costs
> , sum(Sales) as sales
>

Re: Help on writing a sql statement

[SGreen]

(my response bottom-posted. See below - SG)
"Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 09:16:13 PM:

> Hi Shawn .. Thanks for the offer. I am attaching the info that you
> requested.
> 
> mysql> SHOW CREATE TABLE salesmaster\G;
> *** 1. row ***
>Table: salesmaster
> Create Table: CREATE TABLE `salesmaster` (
>   `ID` int(10) NOT NULL auto_increment,
>   `PostingDate` datetime default NULL,
>   `PostingDateJulian` double(15,5) default NULL,
>   `CustNo` varchar(10) default NULL,
>   `ProdNo` varchar(5) default NULL,
>   `SalesMan` char(2) default NULL,
>   `Branch` int(10) default NULL,
>   `Kilos` decimal(19,4) default '0.',
>   `Sales` decimal(19,4) default '0.',
>   `Cost` decimal(19,4) default '0.',
>   PRIMARY KEY  (`ID`),
>   KEY `PostingDate` (`PostingDate`),
>   KEY `CustNo` (`CustNo`),
>   KEY `ProdNo` (`ProdNo`),
>   KEY `Branch` (`Branch`)
> )
> 
> 
> mysql> SHOW CREATE TABLE customerintermediate\G;
> *** 1. row ***
>Table: customerintermediate
> Create Table: CREATE TABLE `customerintermediate` (
>   `ID` int(10) NOT NULL auto_increment,
>   `CustNo` varchar(10) default NULL,
>   `CustName` varchar(120) default NULL,
>   `CustShipAdd1` varchar(50) default NULL,
>   `CustShipAdd2` varchar(50) default NULL,
>   `CustShipAdd3` varchar(50) default NULL,
>   `CustShipPostal` varchar(50) default NULL,
>   `Dept` int(10) default NULL,
>   `Class` int(10) default NULL,
>`Branch` int(10) default NULL,
>   PRIMARY KEY  (`ID`),
>   KEY `CustNo` (`CustNo`),
>   KEY `Branch` (`Branch`),
>   KEY `Dept` (`Dept`)
> )
> 
> 
> 
> mysql> SHOW CREATE TABLE productintermediate\G;
> *** 1. row ***
>Table: productintermediate
> Create Table: CREATE TABLE `productintermediate` (
>   `ID` int(10) NOT NULL auto_increment,
>   `ProdNo` varchar(5) default NULL,
>   `Description` varchar(255) default NULL,
>   `Status` smallint(5) default NULL,
>`Branch` int(10) default NULL,
>   `Source` int(10) default NULL,
>   `Main` int(10) default NULL,
>   `Report` int(10) default NULL,
>   PRIMARY KEY  (`ID`),
>   KEY `ProdNo` (`ProdNo`),
>   KEY `Branch` (`Branch`)
> )
> 
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: "Imran" <[EMAIL PROTECTED]>
> Cc: 
> Sent: Monday, October 10, 2005 4:17 PM
> Subject: Re: Help on writing a sql statement
> 
> 
> > "Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 03:52:21 PM:
> >
> > > Hi all:
> > > I need some help in writing a sql statement.
> > >
> > > I have three tables (Sales, Cust and Product). The sales table 
contains
> > a
> > > large volume of data and I want to create a sql to group the sales 
table
> > > then join the resultant to both the Cust and Prod and to have 
additional
> > > fields selected from the Cust and Prod.
> > >
> > > So in effect something like (obviously syntax is wrong)
> > >
> > > Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as 
sales,
> > > sm.date
> > >
> > > from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group by
> > > sm.prodno, sm.custno, sm.date ,
> > >
> > > (Select prod.desc, prod.code, cust.custno, cust.name from cust, 
Prod)
> > left
> > > join sm.prodno = prod.code left join sm.custno=cust.custno) …
> > >
> > > Any help would be greatly appreciated.
> > >
> >
> > OK, you know you need a GROUP BY, that's good. You also recognized you
> > needed to JOIN a few tables together, also good. There are at least 
two
> > ways to do what you ask. One is a fairly complex query that does it 
all in
> > one statement (might take a long time to compute) the other is a 
sequence
> > of two simpler statements. I think the two-statement solution will be
> > easier to understand and maintain so I would prefer to go over that.
> > However, in order to provide an example of either method I will need 
more
> > information from you.
> >
> > From the CLI (command line client), please provide the output from 
these
> > three commands:
> >
> > SHOW CREATE TABLE sales\G;
> > SHOW CREATE TABLE cust\G;
> > SHOW CREATE TABLE product\G;
> >
> > That will tell me exactly which columns live on which tables and where 
you
> > do or do not have any indexes. Good indexes will make or break the
> > performance of your 

Re: Help on writing a sql statement

[Imran]

Hi Shawn .. Thanks for the offer. I am attaching the info that you
requested.

mysql> SHOW CREATE TABLE salesmaster\G;
*** 1. row ***
   Table: salesmaster
Create Table: CREATE TABLE `salesmaster` (
  `ID` int(10) NOT NULL auto_increment,
  `PostingDate` datetime default NULL,
  `PostingDateJulian` double(15,5) default NULL,
  `CustNo` varchar(10) default NULL,
  `ProdNo` varchar(5) default NULL,
  `SalesMan` char(2) default NULL,
  `Branch` int(10) default NULL,
  `Kilos` decimal(19,4) default '0.',
  `Sales` decimal(19,4) default '0.',
  `Cost` decimal(19,4) default '0.',
  PRIMARY KEY  (`ID`),
  KEY `PostingDate` (`PostingDate`),
  KEY `CustNo` (`CustNo`),
  KEY `ProdNo` (`ProdNo`),
  KEY `Branch` (`Branch`)
)


mysql> SHOW CREATE TABLE customerintermediate\G;
*** 1. row ***
   Table: customerintermediate
Create Table: CREATE TABLE `customerintermediate` (
  `ID` int(10) NOT NULL auto_increment,
  `CustNo` varchar(10) default NULL,
  `CustName` varchar(120) default NULL,
  `CustShipAdd1` varchar(50) default NULL,
  `CustShipAdd2` varchar(50) default NULL,
  `CustShipAdd3` varchar(50) default NULL,
  `CustShipPostal` varchar(50) default NULL,
  `Dept` int(10) default NULL,
  `Class` int(10) default NULL,
   `Branch` int(10) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `CustNo` (`CustNo`),
  KEY `Branch` (`Branch`),
  KEY `Dept` (`Dept`)
)



mysql> SHOW CREATE TABLE productintermediate\G;
*** 1. row ***
   Table: productintermediate
Create Table: CREATE TABLE `productintermediate` (
  `ID` int(10) NOT NULL auto_increment,
  `ProdNo` varchar(5) default NULL,
  `Description` varchar(255) default NULL,
  `Status` smallint(5) default NULL,
   `Branch` int(10) default NULL,
  `Source` int(10) default NULL,
  `Main` int(10) default NULL,
  `Report` int(10) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `ProdNo` (`ProdNo`),
  KEY `Branch` (`Branch`)
)

- Original Message -
From: <[EMAIL PROTECTED]>
To: "Imran" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, October 10, 2005 4:17 PM
Subject: Re: Help on writing a sql statement


> "Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 03:52:21 PM:
>
> > Hi all:
> > I need some help in writing a sql statement.
> >
> > I have three tables (Sales, Cust and Product). The sales table contains
> a
> > large volume of data and I want to create a sql to group the sales table
> > then join the resultant to both the Cust and Prod and to have additional
> > fields selected from the Cust and Prod.
> >
> > So in effect something like (obviously syntax is wrong)
> >
> > Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales,
> > sm.date
> >
> > from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group by
> > sm.prodno, sm.custno, sm.date ,
> >
> > (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod)
> left
> > join sm.prodno = prod.code left join sm.custno=cust.custno) …
> >
> > Any help would be greatly appreciated.
> >
>
> OK, you know you need a GROUP BY, that's good. You also recognized you
> needed to JOIN a few tables together, also good. There are at least two
> ways to do what you ask. One is a fairly complex query that does it all in
> one statement (might take a long time to compute) the other is a sequence
> of two simpler statements. I think the two-statement solution will be
> easier to understand and maintain so I would prefer to go over that.
> However, in order to provide an example of either method I will need more
> information from you.
>
> From the CLI (command line client), please provide the output from these
> three commands:
>
> SHOW CREATE TABLE sales\G;
> SHOW CREATE TABLE cust\G;
> SHOW CREATE TABLE product\G;
>
> That will tell me exactly which columns live on which tables and where you
> do or do not have any indexes. Good indexes will make or break the
> performance of your database. You will not be exposing any data, only the
> design of the tables.
>
> Please remember to CC the list on all responses.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help on writing a sql statement

[SGreen]

"Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 03:52:21 PM:

> Hi all:
> I need some help in writing a sql statement.
> 
> I have three tables (Sales, Cust and Product). The sales table contains 
a
> large volume of data and I want to create a sql to group the sales table
> then join the resultant to both the Cust and Prod and to have additional
> fields selected from the Cust and Prod.
>
> So in effect something like (obviously syntax is wrong)
> 
> Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales,
> sm.date
>
> from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group by
> sm.prodno, sm.custno, sm.date ,
> 
> (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) 
left
> join sm.prodno = prod.code left join sm.custno=cust.custno) …
> 
> Any help would be greatly appreciated.
> 

OK, you know you need a GROUP BY, that's good. You also recognized you 
needed to JOIN a few tables together, also good. There are at least two 
ways to do what you ask. One is a fairly complex query that does it all in 
one statement (might take a long time to compute) the other is a sequence 
of two simpler statements. I think the two-statement solution will be 
easier to understand and maintain so I would prefer to go over that. 
However, in order to provide an example of either method I will need more 
information from you.

From the CLI (command line client), please provide the output from these 
three commands:

SHOW CREATE TABLE sales\G;
SHOW CREATE TABLE cust\G;
SHOW CREATE TABLE product\G;

That will tell me exactly which columns live on which tables and where you 
do or do not have any indexes. Good indexes will make or break the 
performance of your database. You will not be exposing any data, only the 
design of the tables.

Please remember to CC the list on all responses.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Help on writing a sql statement

[Imran]

Hi all:

 

I need some help in writing a sql statement.

 

I have three tables (Sales, Cust and Product). The sales table contains a
large volume of data and I want to create a sql to group the sales table
then join the resultant to both the Cust and Prod and to have additional
fields selected from the Cust and Prod.

 

So in effect something like (obviously syntax is wrong)

 

Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales,
sm.date

from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group by
sm.prodno, sm.custno, sm.date ,

(Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left
join sm.prodno = prod.code left join sm.custno=cust.custno) …

 

Any help would be greatly appreciated.


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date: 10/10/2005
 

Re: A SQL statement to copy a blob from one table to another

[Jasper Bryant-Greene]

C.F. Scheidecker Antunes wrote:

I want to insert the blob file from table1 where id = 1 to table2.
To select the blob I would issue:  select file from table1 where id = 1
To insert I would do: insert into table2 (blob) values(?)


INSERT INTO table2 (blob) SELECT file FROM table1 WHERE id=1

--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

A SQL statement to copy a blob from one table to another

[C.F. Scheidecker Antunes]

Hello everyone:

I have two tables that store a blob.
They both have Id fields.
What I would like to do is, given an id, copy the blob stored on table 
one to table two.
I could do that in my java program by selecting and retrieving the blob 
from table 1 and then issuing
a insert/replace statement to table 2. But it is not elegant. Hence I 
wonder if anyone would have a better

suggestion of something better.

table1:
id - integer
file - blob

table2:
id - integer
file - blob

I want to insert the blob file from table1 where id = 1 to table2.
To select the blob I would issue:  select file from table1 where id = 1
To insert I would do: insert into table2 (blob) values(?)

I am using MySQL 4.1.12.

Thanks,
C.F.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL statement generated from prepared statement

[Darrell Cormier]

On Thursday 18 August 2005 16:37, Gleb Paharenko wrote:
> Hello.
>
> These links might be helpful:
>   http://dev.mysql.com/doc/mysql/en/query-log.html
>   http://dev.mysql.com/doc/mysql/en/debugging-client.html
>   http://dev.mysql.com/doc/mysql/en/debugging-server.html
>
> Use a fresh version of MySQL, because old couldn't
> log queries from prepared statements properly.
>
Thank you, that is what I was looking for.

DC


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL statement generated from prepared statement

[Gleb Paharenko]

Hello.



These links might be helpful:

  http://dev.mysql.com/doc/mysql/en/query-log.html

http://dev.mysql.com/doc/mysql/en/debugging-client.html

http://dev.mysql.com/doc/mysql/en/debugging-server.html



Use a fresh version of MySQL, because old couldn't 

log queries from prepared statements properly.





Darrell Cormier <[EMAIL PROTECTED]> wrote:

> Is there a way to get MySQL to report the SQL statement it receives from 

> a prepared statement execution (from a program using the C-API )?  I am 

> wondering what the SQL statement looks like that is being generated from 

> my program to help debug it.

> 

> Thanks,

> DC

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SQL statement generated from prepared statement

[Darrell Cormier]

Is there a way to get MySQL to report the SQL statement it receives from 
a prepared statement execution (from a program using the C-API )?  I am 
wondering what the SQL statement looks like that is being generated from 
my program to help debug it.


Thanks,
DC

--
Darrell Cormier <[EMAIL PROTECTED]>
Registered Linux user #370711 http://counter.li.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: optimize a sql statement

[Gleb Paharenko]

Hello.





May be I'm wrong, but most of the time, your query is spending in

sorting results (you can check it with SHOW PROCESSLIST). If you can't

change it, you could increase the value of tmp_table_size to use

in-memory tables, if you have enough RAM.









$ $ <[EMAIL PROTECTED]> wrote:

> I analyzed the query plan again.

> ---

> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 348660, 

> 'Using where; Using temporary; Using filesort'

> 1, 'PRIMARY', 'order_line', 'ref', 

> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 

> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'

> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 

> 'PRIMARY,orders_o_c_id','orders_o_c_id', '5', 'func', 1, 'Using where; 

> Using index'

> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 

> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 

> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'

> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'

> -

>In the plan,i find there are 348660 row scan in table orders.And in the 

> sql statement,orders.o_id is be used to join operation and orders.o_c_id be 

> used in the where statement.So i create a index "orders_test" on table 

> orders(o_id and o_c_id).

>After create the index.I execute the explain again.Follow is the 

> result:

> ---

> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'PRIMARY', '4', '', 

> 348660, 'Using where; Using temporary; Using filesort'

> 

> 1, 'PRIMARY', 'order_line', 'ref', 

> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 

> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'

> 

> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 

> 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 

> 'Using where; Using index'

> 

> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 

> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 

> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'

> 

> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'

> 

> 

>   I find the index "order_test" be set in the possible_keys but not set in 

> the keys.

> So i alter the sql statement with "use index" statement.

> Follow is the explain output after alter:

> 

> 

> 1, 'PRIMARY', 'orders', 'range', 'orders_test', 'orders_test', '4', '', 

> 519210, 'Using where; Using index; Using temporary; Using filesort'

> 

> 1, 'PRIMARY', 'order_line', 'ref', 

> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 

> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'

> 

> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 

> 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 

> 'Using where; Using index'

> 

> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 

> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 

> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'

> 

> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'

> --

Re: optimize a sql statement

[王 旭]

sorry,my english is poor.
Follow is my mean.

One,I create a index.(create index orders_test on orders(o_id,o_c_id))

Two,I do explain again.

Follow is result
--
1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'orders_test', '4', 
'', 517890, 'Using where; Using index; Using temporary; Using filesort'


1, 'PRIMARY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'


2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 
'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 
'Using where; Using index'


2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'


3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
--

Is the performance of sql statement be increased?


From: Sebastian <[EMAIL PROTECTED]>
To: 王 旭 <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED],  mysql@lists.mysql.com,  [EMAIL PROTECTED]
Subject: Re: optimize a sql statement
Date: Fri, 22 Jul 2005 01:47:24 -0400

wow consider a different font when posting.. gives me a headache reading
that.

王 旭 wrote:

> I analyzed the query plan again.
> ---
> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '',
> 348660, 'Using where; Using temporary; Using filesort'
> 1, 'PRIMARY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
> 'PRIMARY,orders_o_c_id','orders_o_c_id', '5', 'func', 1, 'Using where;
> Using index'
> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
> -
> In the plan,i find there are 348660 row scan in table orders.And in
> the sql statement,orders.o_id is be used to join operation and
> orders.o_c_id be used in the where statement.So i create a index
> "orders_test" on table orders(o_id and o_c_id).
> After create the index.I execute the explain again.Follow is the 

result:

> ---
> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'PRIMARY',
> '4', '', 348660, 'Using where; Using temporary; Using filesort'
>
> 1, 'PRIMARY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
> 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1,
> 'Using where; Using index'
>
> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
> 




>
>
> I find the index "order_test" be set in the possible_keys but not set
> in the keys.
> So i alter the sql statement with "use index" statement.
> Follow is the explain output after alter:
> 

---

Re: optimize a sql statement

[Sebastian]

wow consider a different font when posting.. gives me a headache reading
that.

王 旭 wrote:

> I analyzed the query plan again.
> ---
> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '',
> 348660, 'Using where; Using temporary; Using filesort'
> 1, 'PRIMARY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
> 'PRIMARY,orders_o_c_id','orders_o_c_id', '5', 'func', 1, 'Using where;
> Using index'
> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
> -
> In the plan,i find there are 348660 row scan in table orders.And in
> the sql statement,orders.o_id is be used to join operation and
> orders.o_c_id be used in the where statement.So i create a index
> "orders_test" on table orders(o_id and o_c_id).
> After create the index.I execute the explain again.Follow is the result:
> ---
> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'PRIMARY',
> '4', '', 348660, 'Using where; Using temporary; Using filesort'
>
> 1, 'PRIMARY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
> 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1,
> 'Using where; Using index'
>
> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
> 
>
>
> I find the index "order_test" be set in the possible_keys but not set
> in the keys.
> So i alter the sql statement with "use index" statement.
> Follow is the explain output after alter:
> 
>
>
> 1, 'PRIMARY', 'orders', 'range', 'orders_test', 'orders_test', '4',
> '', 519210, 'Using where; Using index; Using temporary; Using filesort'
>
> 1, 'PRIMARY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
> 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1,
> 'Using where; Using index'
>
> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
> -
>
>
> Performance maybe much more bad.
> Now i have two question.
> One,Are there any probability for optimization performance using this
> way?
> Two,I am not able to calculate the cost time with the explain output.I
> have learned the knowlege about "7.2.2. Estimating Query Performance"
> in help.But i ca

Re: optimize a sql statement

[王 旭]

I analyzed the query plan again.
---
1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 348660, 
'Using where; Using temporary; Using filesort'
1, 'PRIMARY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 
'PRIMARY,orders_o_c_id','orders_o_c_id', '5', 'func', 1, 'Using where; 
Using index'
2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'

3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
-
   In the plan,i find there are 348660 row scan in table orders.And in the 
sql statement,orders.o_id is be used to join operation and orders.o_c_id be 
used in the where statement.So i create a index "orders_test" on table 
orders(o_id and o_c_id).
   After create the index.I execute the explain again.Follow is the 
result:

---
1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'PRIMARY', '4', '', 
348660, 'Using where; Using temporary; Using filesort'


1, 'PRIMARY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'


2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 
'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 
'Using where; Using index'


2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'


3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'


  I find the index "order_test" be set in the possible_keys but not set in 
the keys.

So i alter the sql statement with "use index" statement.
Follow is the explain output after alter:


1, 'PRIMARY', 'orders', 'range', 'orders_test', 'orders_test', '4', '', 
519210, 'Using where; Using index; Using temporary; Using filesort'


1, 'PRIMARY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'


2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 
'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 
'Using where; Using index'


2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'


3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
-

Performance maybe much more bad.
Now i have two question.
One,Are there any probability for optimization performance using this way?
Two,I am not able to calculate the cost time with the explain output.I have 
learned the knowlege about "7.2.2. Estimating Query Performance" in 
help.But i can't understand.


thanks


From: 王 旭 <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
CC: mysql@lists.mysql.com, [EMAIL PROTECTED]
Subject: Re: optimize a sql statement
Date: Thu, 21 Jul 2005 18:46:32 +0800

Thank you SGreen.But i can'optimize the sql statement like your way 
because this query must describe in one sql statement.So i think i 
maybe optimize this sql statement through creati

Re: optimize a sql statement

[王 旭]

Thank you SGreen.But i can'optimize the sql statement like your way because 
this query must describe in one sql statement.So i think i maybe optimize 
this sql statement through creating high efficiency index or describe this 
sql statement in other method. Rewrite this sql statement must in one sql 
statement.


Follow is the original sql statement:
-
SELECT ol_i_id
FROM orders, order_line
WHERE orders.o_id = order_line.ol_o_id  and o_id>0 AND 
not(order_line.ol_i_id = 5000)

 AND orders.o_c_id
 IN (SELECT o_c_id FROM orders, order_line
 WHERE orders.o_id = order_line.ol_o_id
   AND orders.o_id > (SELECT MAX(o_id)-1 FROM orders) AND 
order_line.ol_i_id = 5000)

GROUP BY ol_i_id
ORDER BY SUM(ol_qty)DESC limit 5
-
Follow is the original explain output:

--
1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 519414, 
'Using where; Using temporary; Using filesort'


1, 'PRIMARY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 
'tpcw.orders.o_id', 1, 'Using where'


2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 
'orders_o_c_id', '5', 'func', 1, 'Using where; Using index'


2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 
'tpcw.orders.o_id', 1, 'Using where'


3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
---


   In the mail,Pow give me a way.He advise me to build a index on 
orders.o_id and orders.o_c_id.I understanding his mean is table orders join 
table order_line using the index.Because orders.o_c_id be limited in the 
where statemetn,so the index should be filter date before Join operation.In 
this way,the index of orders using much less data to join the table 
order_line.But i can't see the effect when i do like it.I don't know the 
reason.


  Are you have any better way?



From: [EMAIL PROTECTED]
To: 王 旭 <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: Re: optimize a sql statement
Date: Tue, 19 Jul 2005 16:10:18 -0400

王 旭 <[EMAIL PROTECTED]> wrote on 07/18/2005 03:12:28 AM:

> NO effect :-(
>
> >From: pow <[EMAIL PROTECTED]>
> >To: 王 旭 <[EMAIL PROTECTED]>
> >Subject: Re: optimize a sql statement
> >Date: Mon, 18 Jul 2005 11:51:23 +0800
> >
> >Do u have composite index on order_line.ol_o_id AND 

order_line.ol_i_id?

> >
> >You could try that...
> >王 旭 wrote:
> >
> > > Now,I make this sql statement to easy.
> > >
> > > Follow is the sql statement:
> > >
>
-
> > > SELECT ol_i_id FROM orders,order_line
> > > WHERE order_line.ol_o_id = orders.o_id
> > > GROUP BY ol_i_id
> > >
>
-
> > >
> > > Follow is the explain output:
> > >
>
---------
> > > 1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '',
259231,
> > > 'Using index; Using temporary; Using filesort'
> > >
> > > 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id',
> > > 'PRIMARY', '4', 'tpcw.orders.o_id', 1, ''
> > >
>
-
> > > Can it be optimized?
> > >
> > >> From: 王 旭 <[EMAIL PROTECTED]>
> > >> To: mysql@lists.mysql.com
> > >> Subject: optimize a sql statement
> > >> Date: Sat, 16 Jul 2005 18:24:15 +0800
> > >>
> > >> Follow is my sql statement:
> > >>
>
-



>
> > >>
> > >
> > >
> > >>
> > >> SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE
> > >> orders.o_id = order_line.ol

Re: optimize a sql statement

[SGreen]

王 旭 <[EMAIL PROTECTED]> wrote on 07/18/2005 03:12:28 AM:

> NO effect :-(
> 
> >From: pow <[EMAIL PROTECTED]>
> >To: 王 旭 <[EMAIL PROTECTED]>
> >Subject: Re: optimize a sql statement
> >Date: Mon, 18 Jul 2005 11:51:23 +0800
> >
> >Do u have composite index on order_line.ol_o_id AND order_line.ol_i_id?
> >
> >You could try that...
> >王 旭 wrote:
> >
> > > Now,I make this sql statement to easy.
> > >
> > > Follow is the sql statement:
> > > 
> 
-
> > > SELECT ol_i_id FROM orders,order_line
> > > WHERE order_line.ol_o_id = orders.o_id
> > > GROUP BY ol_i_id
> > > 
> 
-
> > >
> > > Follow is the explain output:
> > > 
> 
-
> > > 1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '', 
259231,
> > > 'Using index; Using temporary; Using filesort'
> > >
> > > 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id',
> > > 'PRIMARY', '4', 'tpcw.orders.o_id', 1, ''
> > > 
> 
-
> > > Can it be optimized?
> > >
> > >> From: 王 旭 <[EMAIL PROTECTED]>
> > >> To: mysql@lists.mysql.com
> > >> Subject: optimize a sql statement
> > >> Date: Sat, 16 Jul 2005 18:24:15 +0800
> > >>
> > >> Follow is my sql statement:
> > >> 
> 
-
> 
> > >>
> > >
> > >
> > >>
> > >> SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE
> > >> orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT
> > >> MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) 
AND
> > >> orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE
> > >> orders.o_id = order_line.ol_o_id and orders.o_id > (SELECT
> > >> MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP 
BY
> > >> ol_i_id ORDER BY sumolqty DESC limit 50
> > >> 
> 
--
> 
> > >>
> > >
> > >
> > >>
> > >> follows are explain output:
> > >> 
> 
--
> 
> > >>
> > >
> > >
> > >>
> > >> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '',
> > >> 19398, 'Using where; Using temporary; Using filesort'
> > >>
> > >> 1, 'PRIMARY', 'order_line', 'ref',
> > >> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id',
> > >> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
> > >>
> > >> 3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id',
> > >> 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index'
> > >>
> > >> 3, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> > >> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id',
> > >> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
> > >>
> > >> 4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized 
> away'
> > >>
> > >> 2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized 
> away'
> > >> 
> 
--
> 
> > >>
> > >
> > >
> > >>
> > >>
> > >> This sql statement performance is too bad.Please help me to 
optimize
> > >> it .
> > >>
> > >> thanks!
> > >&

Re: optimize a sql statement

[王 旭]

NO effect :-(


From: pow <[EMAIL PROTECTED]>
To: 王 旭 <[EMAIL PROTECTED]>
Subject: Re: optimize a sql statement
Date: Mon, 18 Jul 2005 11:51:23 +0800

Do u have composite index on order_line.ol_o_id AND order_line.ol_i_id?

You could try that...
王 旭 wrote:

> Now,I make this sql statement to easy.
>
> Follow is the sql statement:
> 

-

> SELECT ol_i_id FROM orders,order_line
> WHERE order_line.ol_o_id = orders.o_id
> GROUP BY ol_i_id
> 

-

>
> Follow is the explain output:
> 

-

> 1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '', 259231,
> 'Using index; Using temporary; Using filesort'
>
> 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id',
> 'PRIMARY', '4', 'tpcw.orders.o_id', 1, ''
> 

-----

> Can it be optimized?
>
>> From: 王 旭 <[EMAIL PROTECTED]>
>> To: mysql@lists.mysql.com
>> Subject: optimize a sql statement
>> Date: Sat, 16 Jul 2005 18:24:15 +0800
>>
>> Follow is my sql statement:
>> 

-


>>
>
>
>>
>> SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE
>> orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT
>> MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) AND
>> orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE
>> orders.o_id = order_line.ol_o_id and orders.o_id > (SELECT
>> MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY
>> ol_i_id ORDER BY sumolqty DESC limit 50
>> 

--


>>
>
>
>>
>> follows are explain output:
>> 

--


>>
>
>
>>
>> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '',
>> 19398, 'Using where; Using temporary; Using filesort'
>>
>> 1, 'PRIMARY', 'order_line', 'ref',
>> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id',
>> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>>
>> 3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id',
>> 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index'
>>
>> 3, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
>> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id',
>> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>>
>> 4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized 

away'

>>
>> 2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized 

away'
>> 

--


>>
>
>
>>
>>
>> This sql statement performance is too bad.Please help me to optimize
>> it .
>>
>> thanks!
>>
>> _
>> 免费下载 MSN Explorer: http://explorer.msn.com/lccn/
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/[EMAIL PROTECTED]
>>
>
> _
> 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn
>



_
与联机的朋友进行交流，请使用 MSN Messenger:  http://messenger.msn.com/cn  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: optimize a sql statement

[王 旭]

Now,I make this sql statement to easy.

Follow is the sql statement:
-
SELECT ol_i_id FROM orders,order_line
WHERE order_line.ol_o_id = orders.o_id
GROUP BY ol_i_id
-

Follow is the explain output:
-
1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '', 259231, 
'Using index; Using temporary; Using filesort'


1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 'PRIMARY', 
'4', 'tpcw.orders.o_id', 1, ''

---------
Can it be optimized?


From: 王 旭 <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: optimize a sql statement
Date: Sat, 16 Jul 2005 18:24:15 +0800

Follow is my sql statement:
-




SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE 
orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT 
MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) AND 
orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE 
orders.o_id = order_line.ol_o_id  and orders.o_id > (SELECT 
MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY 
ol_i_id ORDER BY sumolqty DESC limit 50

--




follows are  explain output:
--




1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 
19398, 'Using where; Using temporary; Using filesort'


1, 'PRIMARY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'


3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 
'orders_o_c_id', '5', 'func', 1, 'Using where; Using index'


3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'


4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized 
away'


2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized 
away'

--





This sql statement performance is too bad.Please help me to optimize 
it .


thanks!

_
免费下载 MSN Explorer:   http://explorer.msn.com/lccn/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




_
与联机的朋友进行交流，请使用 MSN Messenger:  http://messenger.msn.com/cn  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

optimize a sql statement

[王 旭]

Follow is my sql statement:
-

SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line 
WHERE orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT 
MAX(o_id)-1 FROM orders) 
AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id 
FROM orders, order_line 
WHERE orders.o_id = order_line.ol_o_id  and 
orders.o_id > (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 
5000) 
GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50

--

follows are  explain output:
--

1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 19398, 
'Using where; Using temporary; Using filesort'


1, 'PRIMARY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 
'tpcw.orders.o_id', 1, 'Using where'


3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 
'orders_o_c_id', '5', 'func', 1, 'Using where; Using index'


3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 
'tpcw.orders.o_id', 1, 'Using where'


4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'

2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
--


This sql statement performance is too bad.Please help me to optimize it .

thanks!

_
免费下载 MSN Explorer:   http://explorer.msn.com/lccn/  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

optimize a sql statement

[王 旭]

Follow is my sql statement:
-

SELECT SUM(ol_qty) sumolqty, ol_i_id
FROM orders, order_line
WHERE orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT 
MAX(o_id)-1 FROM orders) AND
NOT (order_line.ol_i_id = 500) AND orders.o_c_id IN (SELECT o_c_id FROM 
orders, order_line
WHERE orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT 
MAX(o_id)-1 FROM orders)
AND order_line.ol_i_id = 500) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 
50



Follow is the explain output:
---

1, 'PRIMARY', 'order_line', 'range', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'PRIMARY', '4', '', 13064, 
'Using where; Using temporary; Using filesort'


1, 'PRIMARY', 'orders', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 
'tpcw.order_line.ol_o_id', 1, 'Using where'


3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 
'orders_o_c_id', '5', 'func', 1, 'Using where; Using index'


3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 
'tpcw.orders.o_id', 1, 'Using where'


4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'

2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'


Using temporary; Using filesort is bad ways.Could i optimize it via index 
or rebuild the sql statement?


Thanks!

_
免费下载 MSN Explorer:   http://explorer.msn.com/lccn/  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to write SQL statement to do such query?

[Michael Stassen]

SQL My wrote:
(B
(B> Hi! dear all,
(B> I encountered a problem with designing SQL statement. For example, I
(B> want to query population information from a "city" table, and the
(B> records returned must be ordered according to the population of the
(B> city. Like this:
(B> +---+-+-+
(B> |capital   | country  | Population |
(B> +---+-++
(B> | Seoul| South Korea   | 9981619 |
(B> |Jakarta  | Indonesia   | 9604900 | 
(B> |Ciudad de M$BqY(Bico   | Mexico  | 8591309 | 
(B> |Moscow | Russian Federation| 8389200 |
(B> |Tokyo| Japan| 7980230 |
(B> |Peking   | China| 7472000 |
(B> |London  | United Kingdom  | 7285000 |
(B> |Cairo | Egypt    | 6789479 | 
(B> 
(B> This is a very simple SQL statement, but I want to add sequence number
(B> to each city, for example Seoul is number 1, Jakarta is number 2 and
(B> so on. Namely like this:
(B> +---+---+-+-+
(B> | |capital   | country  |
(B> Population |
(B> +---+---+-++
(B> | 1  | Seoul| South Korea   | 9981619 |
(B> | 2  |Jakarta  | Indonesia   | 9604900 | 
(B> | 3  |Ciudad de M$BqY(Bico   | Mexico  | 8591309 | 
(B> | 4  |Moscow | Russian Federation| 8389200 |
(B> | 5  |Tokyo| Japan| 7980230 |
(B> | 6  |Peking   | China| 7472000 |
(B> | 7  |London  | United Kingdom  | 7285000 |
(B> | 8  |Cairo | Egypt| 6789479 
(B> | 
(B> 
(B> Then, how do I get such a result?
(B> 
(B> Best wishes
(B> Hongwei Liu
(B> 2005/06/03
(B
(BWith a user variable <http://dev.mysql.com/doc/mysql/en/variables.html>.
(B Something like this:
(B
(B  SET @r = 0;
(B  SELECT @r:[EMAIL PROTECTED] AS rank, capital, country, Population
(B  FROM city
(B  ORDER BY Population DESC;
(B
(BMichael
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to write SQL statement to do such query?

[SQL My]

Hi! dear all,
(BI encountered a problem with designing SQL statement. For example, I
(Bwant to query population information from a "city" table, and the
(Brecords returned must be ordered according to the population of the
(Bcity. Like this:
(B+---+-+-+
(B|capital   | country  | Population |
(B+---+-++
(B| Seoul| South Korea   | 9981619 |
(B|Jakarta  | Indonesia   | 9604900 | 
(B|Ciudad de M$BqY(Bico   | Mexico  | 8591309 | 
(B|Moscow | Russian Federation| 8389200 |
(B|Tokyo| Japan| 7980230 |
(B|Peking   | China| 7472000 |
(B|London  | United Kingdom  | 7285000 |
(B|Cairo | Egypt| 6789479 | 
(B
(BThis is a very simple SQL statement, but I want to add sequence number
(Bto each city, for example Seoul is number 1, Jakarta is number 2 and
(Bso on. Namely like this:
(B+---+---+-+-+
(B| |capital   | country  |
(BPopulation |
(B+---+---+-++
(B| 1  | Seoul| South Korea   | 9981619 |
(B| 2  |Jakarta  | Indonesia   | 9604900 | 
(B| 3  |Ciudad de M$BqY(Bico   | Mexico  | 8591309 | 
(B| 4  |Moscow | Russian Federation| 8389200 |
(B| 5  |Tokyo| Japan| 7980230 |
(B| 6  |Peking   | China| 7472000 |
(B| 7  |London  | United Kingdom  | 7285000 |
(B| 8  |Cairo | Egypt| 6789479 | 
(B
(BThen, how do I get such a result?
(B
(BBest wishes
(BHongwei Liu
(B2005/06/03
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem in a very simple SQL statement

[Mário Gamito]

Arghh... shame on me forever.
I'm speachless.
Mikhail Entaltsev wrote:
Missed FROM clause... ;)
Best regards,
Mikhail.
- Original Message - 
From: "Mário Gamito" <[EMAIL PROTECTED]>
To: 
Sent: Friday, March 11, 2005 1:49 PM
Subject: Problem in a very simple SQL statement


Hi,
Why do i get an error on this statement:
select count(email) where email='[EMAIL PROTECTED]';
The error is:
"ERROR 1064: You have an error in your SQL syntax near 'where
email='[EMAIL PROTECTED]'' at line 1"
The statement seems pretty clear to me, but obviously something's wrong.
What can it be ?
Any help would be apreciated.
Warm Regards,
Mário Gamito
--
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: Problem in a very simple SQL statement

[Jay Blanchard]

[snip]
select count(email) where email='[EMAIL PROTECTED]';
[/snip]

select count(email) FROM TABLE where email='[EMAIL PROTECTED]';

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem in a very simple SQL statement

[Mikhail Entaltsev]

Missed FROM clause... ;)

Best regards,
Mikhail.

- Original Message - 
From: "Mário Gamito" <[EMAIL PROTECTED]>
To: 
Sent: Friday, March 11, 2005 1:49 PM
Subject: Problem in a very simple SQL statement


> Hi,
>
> Why do i get an error on this statement:
>
> select count(email) where email='[EMAIL PROTECTED]';
>
> The error is:
> "ERROR 1064: You have an error in your SQL syntax near 'where
> email='[EMAIL PROTECTED]'' at line 1"
>
> The statement seems pretty clear to me, but obviously something's wrong.
>
> What can it be ?
>
> Any help would be apreciated.
>
> Warm Regards,
> Mário Gamito
>
> -- 
> 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]

Problem in a very simple SQL statement

[Mário Gamito]

Hi,
Why do i get an error on this statement:
select count(email) where email='[EMAIL PROTECTED]';
The error is:
"ERROR 1064: You have an error in your SQL syntax near 'where 
email='[EMAIL PROTECTED]'' at line 1"

The statement seems pretty clear to me, but obviously something's wrong.
What can it be ?
Any help would be apreciated.
Warm Regards,
Mário Gamito
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Mysql 4.1 and the LIMIT sql statement

[Dennis Fogg]

On Nov 17, 2004, Matt Babineau & Dan Nelson discussed:

> > Has anyone run into problems with this sql syntax?
> > 
> > LIMIT -1
> > 
> > I've used this extensively in my code to get back all records rather then
> > specifing a limit. I've done this programmatically with PHP, so all my
> > queries have a limit even if I don't need one, I just have it specify LIMIT
> > -1, but apparently this functionality doesn't seem to work in 4.1???
> 
> From http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html :
> 
>  # LIMIT no longer accepts negative arguments. Use some large number
>(maximum 18446744073709551615) instead of -1.
> 
> not sure why it was changed, though.
> 

This is going to break a fair number of phpMyEdit scripts because
phpMyEdit's default template recommends $opts['inc'] being set to
"Value of -1 lists all records in a table"
which gets converted to a SQL statement using LIMIT 0,-1

This does not make for a smooth migration from MySQL 4.0 to 4.1
and the solution suggested in the documentation is a step backwards
in elegance (IMHO).

Dennis Fogg

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

value of array to sql statement

[sam wun]

Hi list,
There is problem when with executing the sql statement in mysql 5.0.
I m not sure whether version 4.x has the same problem.
The  problem is if I use static (hardcoded) values in the SQL statement, 
mysql returns result for each element in the array.
If I use dynamically allocated value that assigned to the array, the sql 
statement only return result for  the first element of the array.
To illustrate the problem please see the code below.

The following perl code does return result when execute the SQL statement:
@outlets = ("07-6-0057","07-3-0051","07-2-0036");
my $mycustcode = "$outlets[1]"; # also work for [0] or [2]
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};
However if I change the code to be  a bit more dynamic like the following:
In html.pl: # the following value (in the OPTION tag) will be submitted 
to the query1_result.cgi.

while ($aref = $sth->fetchrow_arrayref){
print "[0],>$aref->[1]: $aref->[2]\n";
}
In query1_result.cgi:
sub split_outlets_to_array
{
  my ($s) = @_;
  @outlets = split(',',$s);
  return @outlets;
}
$outlet_str = $in{'outlets'}; # the outlets contains a list of custcodes 
that seperated by comma.
@outlets_array = &split_outlets_to_array($outlet_str);
my $mycustcode = "$outlets_array[0]"; # but value of [1] and [2] can't 
make the following SQL statement return a result.

$sql = qq {
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};
Summary:
The problem with the second case is that outlets_array[0] does make the 
SQL statement return result, but [1] and [2] does not.
With the first case (with hardcoded values), all elements of 
outlets_array does make the SQL return result.
There may be problem in the code $in{'outlets'}. However from printing 
each element of the array on the html page, I found nothing wrong with 
the value, they are all printed in the following format on the html page:
outlets[0]: 07-6-0057
outlets[1]: 07-3-0051
outlets[2]: 07-2-0036

I don't know what caused this error when execute it with the SQL statement.
I tried to turn on warninig with -w, but not sure how to see them when 
running in web browser.

Thanks
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]