RE: creating temp file, modifying data and putting into other table

[Kerry Frater]

Sorry the lines and error were hidden in my last message. The 3 lines
typed in the browser query are:

create temporary table ttable1 (select * from testnames where ref='ABCDE');
update ttable1 set ref='12345678';
select * from ttable1;

cursor on 1st line , click execute - no error
cursor on 2nd line , click execute - error table does not exist mydb.ttable1
cursor on 3rd line , click execute - error table does not exist mydb.ttable1

So my gues is that the browser executes the 1st line then after completion
of the statement the temp table is destroyed. i.e. guessing that browser
creates a session to execute the line in?

Kerry




-Original Message-
From: Saravanan [mailto:[EMAIL PROTECTED] 
Sent: 18 January 2008 18:18
To: 'Sebastian Mendel'; 'MySql'; [EMAIL PROTECTED]
Subject: RE: creating temp file, modifying data and putting into other table

Kerry,

Post the error you get.

First select the database you are going to use in the right panel.
execute the create table statement.
update the table.
execute the select statement as last.

Saravanan



--- On Sat, 1/19/08, Kerry Frater [EMAIL PROTECTED] wrote:

 From: Kerry Frater [EMAIL PROTECTED]
 Subject: RE: creating temp file, modifying data and putting into other
table
 To: [EMAIL PROTECTED], 'Sebastian Mendel'
[EMAIL PROTECTED], 'MySql' mysql@lists.mysql.com
 Date: Saturday, January 19, 2008, 12:45 AM
 Yes it is the mysql browser. I have tried one after the
 other with the same
 result.
 
 So it looks as though I am using the wrong testbed
 
 Kerrry
 
 -Original Message-
 From: Saravanan [mailto:[EMAIL PROTECTED] 
 Sent: 18 January 2008 15:12
 To: 'Sebastian Mendel'; 'MySql';
 [EMAIL PROTECTED]
 Subject: RE: creating temp file, modifying data and putting
 into other table
 
 Hi,
 
 Are you using mysql browser? If yes. It will run only the
 current line
 statement. It will not execute all the three statements. So
 try one after
 the other.
 
 Saravanan
 
 
 --- On Fri, 1/18/08, Kerry Frater
 [EMAIL PROTECTED] wrote:
 
  From: Kerry Frater [EMAIL PROTECTED]
  Subject: RE: creating temp file, modifying data and
 putting into other
 table
  To: 'Sebastian Mendel'
 [EMAIL PROTECTED],
 'MySql'
 mysql@lists.mysql.com
  Date: Friday, January 18, 2008, 9:33 PM
  I have just tried it with lowercase with the same
 result.
  
  To test I have opened up the query browser and typed 3
  lines
  
  create temporary table ttable1 (select * from
 testnames
  where ref='ABCDE');
  update ttable1 set ref='12345678';
  select * from ttable1;
  
  I leave the cursor on the 3rd line and click on
 execute. I
  get the result
  
  Testdb.ttable1 doesn't exist error 1146
  
  If I leave the cursor on the first line then it
 appears to
  execute the first
  line as I do not get an error but no data is shown.
  
  Does the browser run a script? Is that why I am
 getting
  problems and I
  should be testing in another way?
  
  Kerry
  
  -Original Message-
  From: Sebastian Mendel
 [mailto:[EMAIL PROTECTED] 
  Sent: 18 January 2008 13:24
  To: [EMAIL PROTECTED]; 'MySql'
  Subject: Re: creating temp file, modifying data and
 putting
  into other table
  
  Kerry Frater schrieb:
   Can someone please advise. I am looking to create
 a
  multiuser friendly
  way
   of getting a subset number of rows from a table
 into
  another whilst making
  a
   modification.
   
   I thought that this could be done using a
 temporary
  table in a batch
  script
   that is unique to that session e.g.
   
   create temporary table Ttable1 (select * from
  masterlist where
  ref='ABCDE');
   update Ttable1 set ref='SMI0C001';
   insert into sublist select * from Ttable1;
   drop Ttable1;
   
   I know the above syntax doesn't work but it
 shows
  the steps I am looking
  to
   take. 
   
   Hope this makes enough sense to be able to
 answer.
  
  did you tried with lowercase table names (ttable1)
 too?
  
  -- 
  Sebastian
  
  
  -- 
  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]
 
 
  


 
 Looking for last minute shopping deals?  
 Find them fast with Yahoo! Search.
 http://tools.search.yahoo.com/newsearch/category.php?category=shopping
 
 -- 
 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]


 


Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs


-- 
MySQL General Mailing List
For list archives: http

RE: creating temp file, modifying data and putting into other table

[Kerry Frater]

I am looking to retest the code using the mysql command line interpretor
instead of the Browser GUI in case the issue is with that.

Thx for your comments.

Kerry




-Original Message-
From: Martin Gainty [mailto:[EMAIL PROTECTED] 
Sent: 18 January 2008 22:18
To: [EMAIL PROTECTED]; 'MySql'
Subject: Re: creating temp file, modifying data and putting into other table

the default DB is mysql
to verify execute mysql client and then show the databases

mysql show databases;
++
| Database   |
++
| information_schema |
| catalog|
| mysql  |
| petclinic  |
| test   |
++
5 rows in set (0.02 sec)

now if I want to create a ttable in the test DB first I must connect to the
DB e.g.
mysql connect test;
Connection id:21
Current database: test

now ALL SQL Statements such as creates/inserts/updates/deletes/selects will
work using the test DB
(you must do the same for Testdb Database)

HTH
M-
- Original Message -
From: Kerry Frater [EMAIL PROTECTED]
To: 'Sebastian Mendel' [EMAIL PROTECTED]; 'MySql'
mysql@lists.mysql.com
Sent: Friday, January 18, 2008 10:03 AM
Subject: RE: creating temp file, modifying data and putting into other table


 I have just tried it with lowercase with the same result.

 To test I have opened up the query browser and typed 3 lines

 create temporary table ttable1 (select * from testnames where
ref='ABCDE');
 update ttable1 set ref='12345678';
 select * from ttable1;

 I leave the cursor on the 3rd line and click on execute. I get the result

 Testdb.ttable1 doesn't exist error 1146

 If I leave the cursor on the first line then it appears to execute the
first
 line as I do not get an error but no data is shown.

 Does the browser run a script? Is that why I am getting problems and I
 should be testing in another way?

 Kerry

 -Original Message-
 From: Sebastian Mendel [mailto:[EMAIL PROTECTED]
 Sent: 18 January 2008 13:24
 To: [EMAIL PROTECTED]; 'MySql'
 Subject: Re: creating temp file, modifying data and putting into other
table

 Kerry Frater schrieb:
  Can someone please advise. I am looking to create a multiuser friendly
 way
  of getting a subset number of rows from a table into another whilst
making
 a
  modification.
 
  I thought that this could be done using a temporary table in a batch
 script
  that is unique to that session e.g.
 
  create temporary table Ttable1 (select * from masterlist where
 ref='ABCDE');
  update Ttable1 set ref='SMI0C001';
  insert into sublist select * from Ttable1;
  drop Ttable1;
 
  I know the above syntax doesn't work but it shows the steps I am looking
 to
  take.
 
  Hope this makes enough sense to be able to answer.

 did you tried with lowercase table names (ttable1) too?

 --
 Sebastian


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


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




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

RE: creating temp file, modifying data and putting into other table

[Kerry Frater]

Yes it is the mysql browser. I have tried one after the other with the same
result.

So it looks as though I am using the wrong testbed

Kerrry

-Original Message-
From: Saravanan [mailto:[EMAIL PROTECTED] 
Sent: 18 January 2008 15:12
To: 'Sebastian Mendel'; 'MySql'; [EMAIL PROTECTED]
Subject: RE: creating temp file, modifying data and putting into other table

Hi,

Are you using mysql browser? If yes. It will run only the current line
statement. It will not execute all the three statements. So try one after
the other.

Saravanan


--- On Fri, 1/18/08, Kerry Frater [EMAIL PROTECTED] wrote:

 From: Kerry Frater [EMAIL PROTECTED]
 Subject: RE: creating temp file, modifying data and putting into other
table
 To: 'Sebastian Mendel' [EMAIL PROTECTED], 'MySql'
mysql@lists.mysql.com
 Date: Friday, January 18, 2008, 9:33 PM
 I have just tried it with lowercase with the same result.
 
 To test I have opened up the query browser and typed 3
 lines
 
 create temporary table ttable1 (select * from testnames
 where ref='ABCDE');
 update ttable1 set ref='12345678';
 select * from ttable1;
 
 I leave the cursor on the 3rd line and click on execute. I
 get the result
 
 Testdb.ttable1 doesn't exist error 1146
 
 If I leave the cursor on the first line then it appears to
 execute the first
 line as I do not get an error but no data is shown.
 
 Does the browser run a script? Is that why I am getting
 problems and I
 should be testing in another way?
 
 Kerry
 
 -Original Message-
 From: Sebastian Mendel [mailto:[EMAIL PROTECTED] 
 Sent: 18 January 2008 13:24
 To: [EMAIL PROTECTED]; 'MySql'
 Subject: Re: creating temp file, modifying data and putting
 into other table
 
 Kerry Frater schrieb:
  Can someone please advise. I am looking to create a
 multiuser friendly
 way
  of getting a subset number of rows from a table into
 another whilst making
 a
  modification.
  
  I thought that this could be done using a temporary
 table in a batch
 script
  that is unique to that session e.g.
  
  create temporary table Ttable1 (select * from
 masterlist where
 ref='ABCDE');
  update Ttable1 set ref='SMI0C001';
  insert into sublist select * from Ttable1;
  drop Ttable1;
  
  I know the above syntax doesn't work but it shows
 the steps I am looking
 to
  take. 
  
  Hope this makes enough sense to be able to answer.
 
 did you tried with lowercase table names (ttable1) too?
 
 -- 
 Sebastian
 
 
 -- 
 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]


 


Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-- 
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: creating temp file, modifying data and putting into other table

[Kerry Frater]

I have just tried it with lowercase with the same result.

To test I have opened up the query browser and typed 3 lines

create temporary table ttable1 (select * from testnames where ref='ABCDE');
update ttable1 set ref='12345678';
select * from ttable1;

I leave the cursor on the 3rd line and click on execute. I get the result

Testdb.ttable1 doesn't exist error 1146

If I leave the cursor on the first line then it appears to execute the first
line as I do not get an error but no data is shown.

Does the browser run a script? Is that why I am getting problems and I
should be testing in another way?

Kerry

-Original Message-
From: Sebastian Mendel [mailto:[EMAIL PROTECTED] 
Sent: 18 January 2008 13:24
To: [EMAIL PROTECTED]; 'MySql'
Subject: Re: creating temp file, modifying data and putting into other table

Kerry Frater schrieb:
 Can someone please advise. I am looking to create a multiuser friendly
way
 of getting a subset number of rows from a table into another whilst making
a
 modification.
 
 I thought that this could be done using a temporary table in a batch
script
 that is unique to that session e.g.
 
 create temporary table Ttable1 (select * from masterlist where
ref='ABCDE');
 update Ttable1 set ref='SMI0C001';
 insert into sublist select * from Ttable1;
 drop Ttable1;
 
 I know the above syntax doesn't work but it shows the steps I am looking
to
 take. 
 
 Hope this makes enough sense to be able to answer.

did you tried with lowercase table names (ttable1) too?

-- 
Sebastian


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

creating temp file, modifying data and putting into other table

[Kerry Frater]

Can someone please advise. I am looking to create a multiuser friendly way
of getting a subset number of rows from a table into another whilst making a
modification.

I thought that this could be done using a temporary table in a batch script
that is unique to that session e.g.

create temporary table Ttable1 (select * from masterlist where ref='ABCDE');
update Ttable1 set ref='SMI0C001';
insert into sublist select * from Ttable1;
drop Ttable1;

I know the above syntax doesn't work but it shows the steps I am looking to
take. 

Hope this makes enough sense to be able to answer.

Regards


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

RE: creating temp file, modifying data and putting into other table

[Kerry Frater]

Thanks for the input Jochem.

I am testing the code using the MySQL Query Browser 1.1.20 and putting the
script in a single Query window over multiple lines.

The first line
create temporary table Ttable1 (select * from masterlist where ref='ABCDE');

works fine (well I don't get any errors). The problem is that when it
processes the second line

update Ttable1 set ref='SMI0C001';

it tells me that Ttable1 doesn't exist. I thought that temporary files exist
for the session? Or am I testing the functionality the wrong way?

Kerry


-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED] 
Sent: 17 January 2008 19:34
To: MySql
Subject: Re: creating temp file, modifying data and putting into other table

On Jan 17, 2008 2:22 PM, Kerry Frater wrote:
 Can someone please advise. I am looking to create a multiuser friendly
way
 of getting a subset number of rows from a table into another whilst making
a
 modification.

 create temporary table Ttable1 (select * from masterlist where
ref='ABCDE');
 update Ttable1 set ref='SMI0C001';
 insert into sublist select * from Ttable1;
 drop Ttable1;

How about:
INSERT INTO sublist (ref, field1, field2, field3)
SELECT
  'SMI0C001'
  , field1
  , field2
  , field3
FROM
  masterlist
WHERE
  ref='ABCDE'
;

Jochem

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

group by two cols 1st desc and 3nd asc

[Kerry Frater]

I have a query to extract some data. Two columns include a setup date
(setupdt) and a completed data (compdt).

I would like the data grouped to get sub-totals but shown in different
orders/ I would like the compdt with the newest first i.e. desc order and
setup date with the oldest first i.e. asc order

 

I have tried various combinations of

 

SELECT . WHERE . GROUP BY compdt DESC, setupdt ASC

 

Or I have GROUP BY with ORDER BY mixed with various combinations. I have yet
to get the correct results. I am using 5.0.45 under WAMP.

 

Can someone tell me if what I want is possible?

 

Kerry

RE: SELECT single row from 2 tables with WHERE clause

[Kerry Frater]

Many thanks Peter. That's the definition I was after.

Kerry
  -Original Message-
  From: Peter K AGANYO [mailto:[EMAIL PROTECTED] Behalf Of Peter K AGANYO
  Sent: 19 February 2007 00:35
  To: [EMAIL PROTECTED]
  Cc: mysql@lists.mysql.com
  Subject: Re: SELECT single row from 2 tables with WHERE clause


  Hi Kerry,

  Try this:

  SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1 LEFT JOIN table2
AS t2 ON t1.linkfield=t2.linkfield WHERE t1.lookup='Z'

  Without the WHERE condition this would return all 1000 rows of table 1
since A Left join returns all rows of the left of the conditional even if
there is no right column to match. but t1.lookup='Z' constrains this to
only the one row of table one with lookup equal to 'Z'.

  Enjoy

  Peter


  On 2/17/07, Kerry Frater [EMAIL PROTECTED] wrote:
I am trying to select a particular row from a table and include a column
for
aq second table but I cannot get the result I am after.

I have table1 with 1000 rows and table2 with 12 rows. The
relationship
between the tables is a column linkedfield. Table1 has a unique key
called
lookup

If I use the code
SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
where (t1.lookup='Z')
I get a result of 12 rows (as expected)

SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
where (t1.linkfield=t2.linkfield)
I get 1000 rows as expected

SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
where (t1.lookup='Z') and (t1.linkfield=t2.linkfield)
returns 1 row if there is an active link between the two tables and

returns 0 rows if there isn't.

This is where I am stuck. In the last example I would like the 1 row
whether
there is an active link or not. The difference will be simply that the
t2desc rsulting column will be blank or contain a value.

Can anyone help me with the logic?

Kerry

SELECT single row from 2 tables with WHERE clause

[Kerry Frater]

I am trying to select a particular row from a table and include a column for
aq second table but I cannot get the result I am after.

I have table1 with 1000 rows and table2 with 12 rows. The relationship
between the tables is a column linkedfield. Table1 has a unique key called
lookup

If I use the code
SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
where (t1.lookup='Z')
I get a result of 12 rows (as expected)

SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
where (t1.linkfield=t2.linkfield)
I get 1000 rows as expected

SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
where (t1.lookup='Z') and (t1.linkfield=t2.linkfield)
returns 1 row if there is an active link between the two tables and

returns 0 rows if there isn't.

This is where I am stuck. In the last example I would like the 1 row whether
there is an active link or not. The difference will be simply that the
t2desc rsulting column will be blank or contain a value.

Can anyone help me with the logic?

Kerry


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

mysql v5 math a bit out. How do I round the info to become correct

[Kerry Frater]

I am running a small procedure for set jobs that calculates a running total
for me to display. It works fine in most cases but the math concerns me.
I am using 5.0.18 on my Windows PC and the latest 5.x on my Linux server
(both test machines).
The finance table is an InnoDb table.
CreditAmount and Debitamount are both fields set to FLOAT.


The SQL code called from my program

   select f.*,@rbal:[EMAIL PROTECTED](debitamount * -1) as runbal
   from (select @rbal:=0) rb,finance f
   where f.jobref='abc1234'
   order by f.jobref,f.inputorder

I have one combination that has as data the following (listed in InputOrder)
DebitAmount   CreditAmount
314.43
  10314.4
1

(at least that is what the select * displays for the table) So I expected to
see the rolling runbal column to be:
-314.43
.97
-0.03

what I actually got was
-314.43
1
-0.000305176

Now I can understand some to be rounding errors and I would like to know how
to tell MySQL that I am only interested to two decimal places in the
evaluated variable but the math of (-314.43 + 10314.4) = 1 is more of a
concern. The last calculated value of runbal could be explained by the use
of float as it is trying to do 1 - 1 which is 0 of course and the
last float would round to that. But of course I shouldn't be starting from
1.

Thanks for any advice

Kerry


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

RE: mysql v5 math a bit out. How do I round the info to become correct

[Kerry Frater]

Thanks for the reference Jay.
Most helpful.

Kerry

-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: 14 December 2006 20:29
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: mysql v5 math a bit out. How do I round the info to become
correct


For exact calculations, you need to use the DECIMAL data type.  See this
section in the manual for the reasons why:

http://dev.mysql.com/doc/refman/5.1/en/precision-math-examples.html

Cheers,

Jay

Kerry Frater wrote:
 I am running a small procedure for set jobs that calculates a running
total
 for me to display. It works fine in most cases but the math concerns me.
 I am using 5.0.18 on my Windows PC and the latest 5.x on my Linux server
 (both test machines).
 The finance table is an InnoDb table.
 CreditAmount and Debitamount are both fields set to FLOAT.


 The SQL code called from my program

select f.*,@rbal:[EMAIL PROTECTED](debitamount * -1) as runbal
from (select @rbal:=0) rb,finance f
where f.jobref='abc1234'
order by f.jobref,f.inputorder

 I have one combination that has as data the following (listed in
InputOrder)
 DebitAmount   CreditAmount
 314.43
   10314.4
 1

 (at least that is what the select * displays for the table) So I expected
to
 see the rolling runbal column to be:
 -314.43
 .97
 -0.03

 what I actually got was
 -314.43
 1
 -0.000305176

 Now I can understand some to be rounding errors and I would like to know
how
 to tell MySQL that I am only interested to two decimal places in the
 evaluated variable but the math of (-314.43 + 10314.4) = 1 is more of
a
 concern. The last calculated value of runbal could be explained by the
use
 of float as it is trying to do 1 - 1 which is 0 of course and the
 last float would round to that. But of course I shouldn't be starting from
 1.

 Thanks for any advice

 Kerry




--
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: SELECT...GROUP BY WITHIN GROUP BY

[Kerry Frater]

Donna,

Just to say thanks.

Played a bit more with the code and options and got most of what I wanted. A
little procedure gives me the rest.

Many thanks for pointing me in the right direction.

Kerry

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 28 November 2006 20:56
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: SELECT...GROUP BY WITHIN GROUP BY


I'm not sure that this is exactly what you want, but I think you can use
the WITH ROLLUP modifier:

select district, town, street, surname, count(surname)
from test5
group by district asc, town asc, street asc, surname asc WITH ROLLUP

Here's a link to the MySQL documentation on WITH  ROLLUP
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

Donna




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

SELECT...GROUP BY WITHIN GROUP BY

[Kerry Frater]

Hope I have the right group.

I am working out how to get groups within groups. e.g.
I have a table with 4 columns C1,C2,C3  C4

I am looking to select data so that I can get

C1 group item

   C2 Group item

  C3 Group Item

 C4 detail

  End of C3 Group Item
  count/totals of C3

   End of C2 Group Item
   count/totals of C2, C3

End of C1 Group item
count/totals of C1, C2, C3

to describe the gorups let us say the 4 columns are
district,town,street,surname.

A full report would be all the surnames in surname order within

street
At the end of each street I would also get the number of surnames in that
street within

town
At the end of each town I would also get the number of streets and
surnames within the town within

district
At the end of each district I would also get the number of towns,
streets and surnames within the district

At the end of selecting all I get the number of districts, towns,
streets and surnames

Thanks

Kerry



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

RE: SELECT...GROUP BY WITHIN GROUP BY

[Kerry Frater]

Thanks for the tip.

I have just entered the SQL statement and it isn't giving me the totals I
want but you have given me something to look up to see if I can use uit to
get what I want. I suppose in programming terms what I am after is:

totsurname=0,totstreet=0,tottown=0,totdistrict=0,tot=0

for each district
   for each town
  for each street
  count surnames within the street
  at end of each street report the district,town,street and no. of
surnames
   count the number of streets
   at the end of each town report the district, town, no. of streets, no. of
surnames
count the number of towns
at the end of each district report the district, no. of towns, no. of
streets, no. surnames

when all rows are processed report the no. of districts, no. of towns, no.
of streets, no. surnames

I was hoping to do this with a clever combination of SELECT, GROUP BY, etc.

Kerry



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 28 November 2006 20:56
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: SELECT...GROUP BY WITHIN GROUP BY


I'm not sure that this is exactly what you want, but I think you can use
the WITH ROLLUP modifier:

select district, town, street, surname, count(surname)
from test5
group by district asc, town asc, street asc, surname asc WITH ROLLUP

Here's a link to the MySQL documentation on WITH  ROLLUP
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

Donna



Kerry Frater [EMAIL PROTECTED]
11/28/2006 02:31 PM
Please respond to
[EMAIL PROTECTED]


To
mysql@lists.mysql.com
cc

Subject
SELECT...GROUP BY WITHIN GROUP BY






Hope I have the right group.

I am working out how to get groups within groups. e.g.
I have a table with 4 columns C1,C2,C3  C4

I am looking to select data so that I can get

C1 group item

   C2 Group item

  C3 Group Item

 C4 detail

  End of C3 Group Item
  count/totals of C3

   End of C2 Group Item
   count/totals of C2, C3

End of C1 Group item
count/totals of C1, C2, C3

to describe the gorups let us say the 4 columns are
district,town,street,surname.

A full report would be all the surnames in surname order within

street
At the end of each street I would also get the number of surnames in
that
street within

town
At the end of each town I would also get the number of streets and
surnames within the town within

district
At the end of each district I would also get the number of towns,
streets and surnames within the district

At the end of selecting all I get the number of districts, towns,
streets and surnames

Thanks

Kerry



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


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information.  Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may violate
applicable laws.  If this email has been received in error, please notify
the sender and delete the information from your system.  The views
expressed in this email are those of the sender and may not necessarily
represent the views of IntelliCare.



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

How to build a single temporary table from 3 tables on the fly

[Kerry Frater]

Can someone help point me in the right direction for this. This isn’t
exactly what I want but once I have the solution to this I can work out the
permutations I need.

How do I select from 3 tables into a single table (consequtive rows not
joined ones) and include a two new columns which is the name of the table
from which the data has been extracted, and a fixed piece of text.

This “select” can be executed by more than one person at a time, so I need
to extract into a transient temp table so that I can view the dataset.

e.g. I have 3 tables containing names and want to extract the rows of a
particular surname so I would have let’s say
select ‘main’, thetablename, surname from t1 into myautogentable
select ‘personal’, thetablename, surname from t2 into myautogentable
select ‘group’, thetablename, surname from t3 into myautogentable

I can then link a database grid in my program to the dataset of the
resulting query.

Kerry



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

RE: How to build a single temporary table from 3 tables on the fly

[Kerry Frater]

Thanks Phil

It gives me a solution and some reading.

Kerry
  -Original Message-
  From: Philip Mather [mailto:[EMAIL PROTECTED]
  Sent: 11 October 2006 10:02
  To: [EMAIL PROTECTED]
  Cc: mysql@lists.mysql.com
  Subject: Re: How to build a single temporary table from 3 tables on the
fly


  Kerry,

How do I select from 3 tables into a single table (consequtive rows not
joined ones) and include a two new columns which is the name of the table
from which the data has been extracted, and a fixed piece of text.
  I'd do something like...

  CREATE TABLE Merged_names
  (
 Temp table definition goes here
  ) ENGINE=MEMORY
  SELECT * FROM
  (
 (
SELECT
   main, hardcodedtablename1, `surname`
FROM
   table1
WHERE
   someCriteria = someOtherCriteria
 )
 UNION ALL
 (
SELECT
   main, hardcodedtablename2, `surname`
FROM
   table2
WHERE
   someCriteria = someOtherCriteria
 )
 UNION ALL
 (
SELECT
   main, hardcodedtablename3, `surname`
FROM
   table3
WHERE
   someCriteria = someOtherCriteria
 )
  ) AS TMP;


This “select” can be executed by more than one person at a time, so I need
to extract into a transient temp table so that I can view the dataset.

e.g. I have 3 tables containing names and want to extract the rows of a
particular surname so I would have let’s say
select ‘main’, thetablename, surname from t1 into myautogentable
select ‘personal’, thetablename, surname from t2 into myautogentable
select ‘group’, thetablename, surname from t3 into myautogentable
  Does that do roughly what you needed?  I'd suggest reading...
  http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
  http://dev.mysql.com/doc/refman/5.0/en/create-table.html, search for and
start reading at CREATE TABLE new_tbl SELECT
  http://dev.mysql.com/doc/refman/5.0/en/union.html

  Regards,
  Phil

RE: update a Blob field using UPDATE

[Kerry Frater]

Thanks for the reference. Sorry for the delay in responding but I had been
away. Yes the mystring$ is built with single strops to quote the content. I
have an issue running this command so I have worked around it by putting the
content of the var mystring$ into a text file and then putting it into the
BLOB field using LOAD_FROM_FILE.

Slower I know but I get no errors.

Kerry

-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: 03 February 2006 11:28
To: mysql@lists.mysql.com
Subject: Re: update a Blob field using UPDATE


Hello.

Have you applied mysql_real_escape_string to your BLOB variable first?
See:
  http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html

I do not see the quotes around mystring$ as well.


Kerry Frater wrote:
 I am importing data from a non MySQL table into MySQL.

 In the table there is a text field of up to length 4000 chars. I have
 defined the column as blob in the MySQL table.
 I can read the text field of the source table into a variable e.g.
 mystring$. The MySQL table has been set, except for this data.

 I thought to use
   SQLString = UPDATE TheTable SET Notes =  + mystring$ +  WHERE
 TheTableRef = ' + Myref$ + ';

 I get error:
 You have an error in the SQL syntax

 I have tried to search the manual for an example of updating a blob column
 from a variable and cannot find one. I don't want to save the content of
the
 var to disk and then load from file because of the time it takes.

 Do I have another option?

 Kerry



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


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

error 1146 X.1 does not exist

[Kerry Frater]

Can someone point me in the right direction.
I had 4.1 running and all was well with my little program. I decided to test
the code with the newer 5.0.18. I updated my DB and then tried to access it
using the MYSQl Control Centre - 0.9.4-Beta and query browser 1.0.4 alpha.

The Administrator allows me to view table structure but not to add any
numerical columns.

Using the Control centre, If I double click on the table I wish to view data
I get two lines in the message area
Empty set (0.00) sec
[local] ERROR 1146: Table 'llcopy.1' doesn't exist

llcopy being my test database name.
These messages are got no matter what the table content is. I do note that
the error message on this app is different numerically to the previous

Also my insert program now fails to insert data. So I extracted one of the
SQL insert commands that worked with 4.1 and manually run it using the
mysql.exe command interface. This is the manual command.

INSERT INTO BanksC
(Bank,BankName,AccountNo,Balance,PMClosingBalance,EOMTempBalance,STTS)
VALUES (C1,Client
Bank,NONE,0.00,0.00,0.00,C);

The error was
ERROR 1054 (42S22): Unknown column 'C1' in 'field list'

The settings of the table are char(2), char(20), char(12),
decimal(12,2),decimal(12,2),decimal(12,2),char(1) respectively.

This is driving me crazy. I am obviously doing something stupid but I have
got so close to the problem I can't see the wood for the trees.

Kerry


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

RE: error 1146 X.1 does not exist

[Kerry Frater]

I have seen the problem with the INSERT syntax. The new version doesn't like
the use of 's to surround text and prefers single quotes.

I still don't see where the dbname.1 error is from

Kerry


-Original Message-
From: Kerry Frater [mailto:[EMAIL PROTECTED]
Sent: 02 February 2006 10:05
To: mysql@lists.mysql.com
Subject: error 1146 X.1 does not exist


Can someone point me in the right direction.
I had 4.1 running and all was well with my little program. I decided to test
the code with the newer 5.0.18. I updated my DB and then tried to access it
using the MYSQl Control Centre - 0.9.4-Beta and query browser 1.0.4 alpha.

The Administrator allows me to view table structure but not to add any
numerical columns.

Using the Control centre, If I double click on the table I wish to view data
I get two lines in the message area
Empty set (0.00) sec
[local] ERROR 1146: Table 'llcopy.1' doesn't exist

llcopy being my test database name.
These messages are got no matter what the table content is. I do note that
the error message on this app is different numerically to the previous

Also my insert program now fails to insert data. So I extracted one of the
SQL insert commands that worked with 4.1 and manually run it using the
mysql.exe command interface. This is the manual command.

INSERT INTO BanksC
(Bank,BankName,AccountNo,Balance,PMClosingBalance,EOMTempBalance,STTS)
VALUES (C1,Client
Bank,NONE,0.00,0.00,0.00,C);

The error was
ERROR 1054 (42S22): Unknown column 'C1' in 'field list'

The settings of the table are char(2), char(20), char(12),
decimal(12,2),decimal(12,2),decimal(12,2),char(1) respectively.

This is driving me crazy. I am obviously doing something stupid but I have
got so close to the problem I can't see the wood for the trees.

Kerry


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

Out of Range value adjusted?

[Kerry Frater]

I am trying to test some code on the new 5.0 version and am getting
problems.

I currently have two MYSQL's running on two differing machines. Both have
the same schema set up by the my own program. The difference is that Machine
1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a fresh
installation of version 5.

My Data insertion code works perfectly well with the Version 5 instance that
was upgraded from version 4, but fails on the pure version 5. The
Administrator interface tells me that the tables are OK and I cannot see any
difference between the V4 upgraded to 5 table against the pure V5 install.

The code being used is
INSERT INTO Invs
(InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
VatRate)
VALUES
 ('1234#6','19991016','C','19990731','\Orig Bill
£728.50\',364.68,'A',17.50);

The table columns are set to
char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2). The
table is MyISAM.

There error produced on the pure version 5 installation is:
Out of Range value adjusted for column VATRate at row 1
and then quits. I have let the installation of MySQL use its defaults and is
of charset latin1 if it is an issue. The above statement does not error on
the upgraded version of MySQL but adds the row with no problem.

The set up program is using a function
   with SQLBatch.SQL do
   begin
  Add('CREATE TABLE Invs (');
  Add('InvRef char(12) default NULL,');
  Add('InvDate date,');
  Add('OutgoingRef char(1),');
  Add('OutgoingRef2 date,');
  Add('BillDescription char(40),');
  Add('BillAmount decimal(12,2),');
  Add('VATCode char(1),');
  Add('VATRate decimal(3,2),');
  Add('  index (OutgoingRef2)');
  Add(') TYPE=MyISAM;');
   end;
   SQLBatch.ExecSql;

This seems to work fine. Any ideas on why it works with one and not the
other?

Kerry


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

update a Blob field using UPDATE

[Kerry Frater]

I am importing data from a non MySQL table into MySQL.

In the table there is a text field of up to length 4000 chars. I have
defined the column as blob in the MySQL table.
I can read the text field of the source table into a variable e.g.
mystring$. The MySQL table has been set, except for this data.

I thought to use
  SQLString = UPDATE TheTable SET Notes =  + mystring$ +  WHERE
TheTableRef = ' + Myref$ + ';

I get error:
You have an error in the SQL syntax

I have tried to search the manual for an example of updating a blob column
from a variable and cannot find one. I don't want to save the content of the
var to disk and then load from file because of the time it takes.

Do I have another option?

Kerry


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

RE: MySQL 5.0 error after upgrade

[Kerry Frater]

Thanks,

I'll look to see how to use the system. But you are right that there is an
issue somewhere given that the automatic code produced by Administrator,
Control Centre and Browser all have problems with communicating with the
tables following the upgrade.

Kerry


-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: 18 January 2006 15:57
To: mysql@lists.mysql.com
Subject: Re: MySQL 5.0 error after upgrade


Hello.

 ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER
CHARACTER SET latin1 COLLATE latin1_general_ci;

This seems like a bug. MySQL Administrator should not assign character
set to integer columns. See:
  http://dev.mysql.com/doc/refman/5.0/en/bug-reports.html


Kerry Frater wrote:
 Can someone help me.

 I was running v4 and just upgraded the version to v5.0.

 Most of my tables are MyISAM with some InnoDb. Most of the MyISAM tables
are
 char fields but a few have integer columns. When trying to create a new
 table using the Administrator 1.1 program it is fine creating columns
which
 are of type char but it fails when creating a column of type integer.
 The error message is
 ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER CHARACTER
SET
 latin1 COLLATE latin1_general_ci;

 Can someone advise me what has changed in the upgrade and how I can get
back
 to using integers?

 kERRY



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


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

error 1064 after upgrade to 5.0 from 4.1

[Kerry Frater]

Sorry I should have said. I get error 1064 whenever I am working with an
integer or real column. char's are fine.

Kerry




Can someone help me.

I was running v4.1 and just upgraded the version to v5.0.

Most of my tables are MyISAM with some InnoDb. Most of the MyISAM tables are
char fields but a few have integer columns. When trying to create a new
table using the Administrator 1.1 program it is fine creating columns which
are of type char but it fails when creating a column of type integer.
The error message is
ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER CHARACTER SET
latin1 COLLATE latin1_general_ci;

Can someone advise me what has changed in the upgrade and how I can get back
to using integers?

kERRY


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

MySQL 5.0 error after upgrade

[Kerry Frater]

Can someone help me.

I was running v4 and just upgraded the version to v5.0.

Most of my tables are MyISAM with some InnoDb. Most of the MyISAM tables are
char fields but a few have integer columns. When trying to create a new
table using the Administrator 1.1 program it is fine creating columns which
are of type char but it fails when creating a column of type integer.
The error message is
ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER CHARACTER SET
latin1 COLLATE latin1_general_ci;

Can someone advise me what has changed in the upgrade and how I can get back
to using integers?

kERRY


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

Crystal Reports MySQL

[Kerry Frater]

I have MySQL  the downloaded ODBC drivers.

I am looking to use Crystal Reports to design  run my own reports on my
tables.

Can anyone tell me if the V11 Standard will happily work with MySQL or do I
need a different version?

Kerry

RE: Crystal Reports MySQL

[Kerry Frater]

Thanks for the reply Scott. I found the press release interesting.

I have V9 Dev version and need runtime version for another site. I can buy
V11 but wasn't sure if the STD version was enough to set a report and run it
with MySQL.

Kerry
  -Original Message-
  From: Scott Pippin [mailto:[EMAIL PROTECTED]
  Sent: 28 April 2005 15:11
  To: [EMAIL PROTECTED]
  Cc: mysql@lists.mysql.com
  Subject: Re: Crystal Reports  MySQL


  I haven't tested it but I am using version 10 with no problems.  See the
announcement made by business objects at the MySQL users conference:
http://www.mysql.com/news-and-events/press-release/release_2005_10.html

  I hope this helps.

  Scott Pippin
  [EMAIL PROTECTED]

   Kerry Frater [EMAIL PROTECTED] 04/28/05 6:09 AM 

  I have MySQL  the downloaded ODBC drivers.

  I am looking to use Crystal Reports to design  run my own reports on my
  tables.

  Can anyone tell me if the V11 Standard will happily work with MySQL or do
I
  need a different version?

  Kerry

Secure access to the Data structures and data within MySQL

[Kerry Frater]

Perhaps someone can provide me with some of his or her experiences if
looking at MySQL to implement a “secure from structure manipulation” in a
commercial application using MySQL.



I am currently evaluating the possible migration of my app to MySQL as the
basis. I already have tested  the creation of structures and exporting of
the 100+ tables and looked at viewing the data via Delphi on internal
networks and over dial-up lines. I have also viewed the creation of “Open
Database” copies that users can do What If  analysis and structure
manipulation with. This has been a successful start I am pleased to say



Part of my is into the security of the data structures. I need to know that
the creation of a structure and data I use that users/hackers would 1) not
be able to access and manipulate the data structures and 2) only access the
data according to the user definitions set under my applications
administration utilities. The reason for this is that the data I deal with
is both sensitive and relatively complex in its relationships. Data
Integrity is vitally important. I do not want anyone having the chance of
manipulating the “live” data structures and data other than via the
application. That is not to say that users cannot manipulate their own data.
In a controlled environment I currently provide an “export” of the data to
an open format (which now includes MySQL) that users can manipulate in any
way, manner or form they like. It is only the Live data that needs to be
closely controlled and handled. This model has proved successful over the
last 13 years but I am now looking for a new database engine. The pricing
model is understood and am happy with the Commercial License approach and
with me looking to roll out 200+ licenses over a 24 month period, this is
not an issue. The issue is can it work in a similar controlled manner as my
current application. If not where are the issues and can I live with the
differences.



One worry is the ability for users/hackers etc to overlay the user access
database i.e. “what to do if you forget the administration password”
scenario. This procedure will then allow open access to the data structures
and the information of the application and the “live” data. I need to block
this capability.



What have others done to keep data structure security within their control
only? Being a Windows house I am really only looking at the Windows version
(at the moment). I use other high level languages, which doesn’t include C++
i.e. I don’t have the compiler to “make my own special MySQL version”. I don
’t really want to create my own special binaries anyway.



I am not sure what encryption models are available. An external encryption
library isn’t really useful because this would block users using 3rd party
report writers to gain read-only access to the data structures to write
their reports. If an internal encryption mechanism is available then I would
like to hear about it.



Sorry about the message length, but I am trying to give a little bit of
background to cover the more obvious “Why don’t you …” or “Do you …”
questions.



Regards



Kerry

update data according to value in other table

[Kerry Frater]

Can someone tell me if this is possible using the UPDATE command

I have a table MASTER and a table called MASTERNOTES. Masternotes contains
the blobs of data linked to MASTER that is stored separately for efficiency.
Not all rows in MASTER has a note and therefore MASTERNOTES is a subset of
MASTER.

I want to add a column in MASTER HaveANote. The value is 'Y' if a note
exists in MASTERNOTES and 'N' if not.

I was wondering if this can be done using UPDATE. The manual doesn't have
any examples that I can see showing an UPDAT if ... example.

In pseudo terms I am looking to do
UPDATE MASTER
SET HaveANote = 'Y' IF MATTERREF EXISTS IN MASTERNOTES

I know how to write this in a program to update the table would like to know
how to do it using SQL syntax.

Thanks

Kerry

RE: How to get the last record from the slected record set

[Kerry Frater]

Hi,

I don'e know if this will help as I am probably only one step ahead of you
here, but if it does great. If not you can just delete it.

How are you going to access the data? Via a program, PHP, using queries?

I am a newbie to this area myself, and use Delphi.

I know that using Delphi with the DB components I can simply tell the
Navigator to get the last record. Fairly straight forward. I have recently
found that using the DB componensts (so I am told) creates a local dataset
which means that ALL rows selected are transferred to a local dataset for
you to have a simple goto last record. i.e. If we have 1 million rows,
each row contains 10 integers then opening a table to the data and telling
it to go to last record will cause 10 million integers on the server to
transfer to a local dataset on your PC/workstation for you to process. This
is not really an issue if the datasets are created on the same computer as
the Server.

(I am learning that) if data transfer is an issue and could cause problems
then a little bit of SQL is more than useful. I continue to learn about the
SQL formats. With the help of another newish MySQL writer (he is one step
ahead of me) Tom gave me some help that I have turned into this.

For workstations needing to gain access to 1 row at a time and NOT wanting
to create a large dataset on my workstation, which may have a slow
connection I do the following (in pseudo code):

Get First Record
  SQL.Text = SELECT * FROM MyTable Where MyField   ORDER BY MyIndex
LIMIT 1;
  ExecuteSQL.Text;

Get Last Record
  SQL.Text = SELECT * FROM MyTable ORDER BY MyIndex DESC LIMIT 1;
  ExecuteSQL.Text;

When I get a record I remember the unique value of the index that I am
scrolling through. Let us say I store the value of MyField of the current
row in a variable called MyKey then the next record is where MyField 
MyKey and you limit the number of rows to return by 1.

Get Next Record
  SQL.Text = SELECT * FROM MyTable Where MyField  MyKey ORDER BY
MyIndex LIMIT 1;
  ExecuteSQL.Text;
  if Dataset.IsEmpty then GetLastRecord(MyMatter,MyIndex);

Get Previous Record
  SQL.Text = SELECT * FROM MyTable Where MyField  MyKey ORDER BY
MyIndex DESC LIMIT 1;
  ExecuteSQL.Text;
  if Dataset.IsEmpty then GetFirstRecord(MyMatter,MyIndex);

The above constructs work if the column you are scrolling through is unique.
The issue is when you have a non unique order e.g. Surname. This is where
Tom's more advanced knowledge of SQL helped be get over the problem.

SELECT * FROM table WHERE MyField = '' AND UniqueId  -1 ORDER BY
MyField,UniqueId LIMIT 0,1

You will need to have a column containing a Unigue ID to do this (indexing
this will also give you extra performance)
The clever bit is that we are creating a temporary sort order which is a
combination of the required field sequence and the unique ID sequence which
will, by definition, give us a order with a Unique sort sequence.

Now you will need to know the values from the current Row for the columns
MyField and UnigueID, let us say OldMyField and OldUniqueID. We will then
get

Get the next record:
SELECT * FROM table WHERE MyField = OldMyField AND UniqueId  OldUniqueID
ORDER BY MyField,UniqueId LIMIT 1

The other Get Record types are then derivations, but you should be able to
write generic function/subtroutines based upon what you want. This is what I
am doing at the moment and the performance over PC's using slow connection
links to not so fast servers is proving to be quite successful.

For my not very big tables I will probably not bother to implement the calls
as I can use the generic components to write quick interfaces as the local
dataset issue won't be a problem.

I hope this makes sense!


Kerry


-Original Message-
From: Manisha Sathe [mailto:[EMAIL PROTECTED]
Sent: 22 August 2004 14:08
To: [EMAIL PROTECTED]
Subject: Re: How to get the last record from the slected record set


yes, but is there any better way of doing it ?

regards
Manisha


- Original Message -
From: Karl Pielorz [EMAIL PROTECTED]
To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, August 23, 2004 8:34 PM
Subject: Re: How to get the last record from the slected record set




 --On 22 August 2004 20:31 +0800 Manisha Sathe [EMAIL PROTECTED]
 wrote:

  I am having more than 10 records in a table. I want to select only first
  top 10 records (depending on one field score) and then want to select
  10th position record.
 
  select * from table1 order by score desc LIMIT 10
 
  This will give me 10 records but then how to get the last record ?

 order by score asc limit 1

 [i.e. turn it around and pick the 1st (which will be the last because it's
 ordered the other way)]

 :-)

 -Kp

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

RE: newbie question on scrolling through a table one record at a time

[Kerry Frater]

Many thanks for your reply Tom.

I will read it more closely over the week-end, but wanted to say thanks
straight away.

Kerry

-Original Message-
From: Tom Horstmann [mailto:[EMAIL PROTECTED]
Sent: 20 August 2004 15:52
To: 'Kerry Frater'; 'MySQL List'
Subject: RE: newbie question on scrolling through a table one record at
a time


Hi Kerry,

 The problem is more fundamental with the scrolling through
 the records/rows of Master. [..] It is not sensible to allow
 200 million pieces of data to be transferred to the Delphi PC
 to build a local Dataset to scroll through.

[..]
 Getting the first row is easy

 select * from Master order by MasterRef limit 1;

It is, but LIMIT mostly is executed after getting all records
meeting the WHERE-condition. That might be quite slow.


 but getting the next record isn't as straight forward. Mainly
 because I have no idea of what the next value of MasterRef
 is. All I know is that I want to get the next row in
 sequence. None of the papers I have or have seen addresses
 this issue. Either this concept is not required in SQL
 programming or it is so obvious that it doesn't need
 explaining. Either way I can't see the wood for the trees.

 If MasterRef is a unique value column then the next record  would be:

 select * from Master order by MasterRef limit 1 where
 MasterRef  MyCurrentMasterRefValue;

 This simply raises questions 1) how to get the previous row
 (presume you use the DESCENDING keyword of the table, 2) how
 to test for Begining and End of Table and 3) what to do if
 the column being ordered on is not unique.

I ran through the same problem the last days (still). This is
how i did it: At first you need a unique key (auto-increment).
I name it id. If you have records that are non-unique by the
column you want to sort them, try to add other columns to sort
on to get them as unique as you can. Having non-unique records
isn't a problem as long there are not many beeing equal.

How to move through records:
Starting with a value of '', always SELECT the record having a
higher value in that columns you sorted on than the last one.
As you said, that's all, if there all records are unique.

You will need LIMIT to get through equal records. It's quite
easy having an example:

record   key
1a
2a
3b
4b
5c

Get the first record (assuming your id starts with 0):
SELECT key FROM table WHERE key='' AND id-1
ORDER BY key,id LIMIT 0,1

Store these:
old_key = key
old_id  = id

Get the next record:
old_key = SELECT key FROM table WHERE key=old_key AND idold_id
ORDER BY key,id LIMIT 1,1

The LIMIT has to be 1 here to get the second record. As long as key
stays equal to old_key, increase the limit by one for each record
fetched. When getting a new value for key, set the limit to 1 again.

You need to find a good relation between the amount of equal records
and the columns you use to sort and move on. 500 records having the
same key will make things slow, but using a long WHERE- clause will
do, too.
I tested this for upto 3 records. If there is an index on all
columns used in the SELECTs, speed seems to be stable upto that size.

hth,

TomH

--
PROSOFT EDV-Loesungen GmbH  Co. KGphone: +49 941 / 78 88 7 - 121
Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0
Geschaeftsfuehrer: Axel-Wilhelm Wegmann  [EMAIL PROTECTED]
AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de
--



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

newbie question on scrolling through a table one record at a time

[Kerry Frater]

Hi all,
I don't think this is the right list for the question but I am hoping
someone in the list will be able to point me in the right direction.

I am testing the use of Delphi with MySQL (using Micoolap's DAC) to access
and manipulate a number of tables in a databse. Some of the more complex
structures I want to do are quite clear on how to implement them with many
papers and books published. My problem is the technique on implementing a
far more fundamental issue.

Let us say I have a couple of tables with a large number of rows (Master and
Detail) with a common (indexed for performance) column MasterRef. Getting
the rows from table Detail is straight forward by using a Query

select * from Detail where Master.MasterRef = Detail.MasterRef

The problem is more fundamental with the scrolling through the records/rows
of Master. Reading previously posted information, it comes to light that if
I open a table to scroll through using an application navigator then the app
creates and uses a local dataset. Not a big issue if the database is local,
on a high speed connection, or has a relatively small number of rows. But
what if Master has 1 million rows with 200 columns. It is not sensible to
allow 200 million pieces of data to be transferred to the Delphi PC to build
a local Dataset to scroll through.

I note there is the concept of LIMIT. This looks good until I try to see how
to implement its usage in the real world. The concept of creating an app
that only works on one (or a small number of rows) at a time is eluding me
at the moment. Getting the first row is easy

select * from Master order by MasterRef limit 1;

but getting the next record isn't as straight forward. Mainly because I have
no idea of what the next value of MasterRef is. All I know is that I want to
get the next row in sequence. None of the papers I have or have seen
addresses this issue. Either this concept is not required in SQL programming
or it is so obvious that it doesn't need explaining. Either way I can't see
the wood for the trees.

If MasterRef is a unique value column then the next record  would be:

select * from Master order by MasterRef limit 1 where MasterRef 
MyCurrentMasterRefValue;

This simply raises questions 1) how to get the previous row (presume you use
the DESCENDING keyword of the table, 2) how to test for Begining and End of
Table and 3) what to do if the column being ordered on is not unique.

Are there any known papers, documents, references, books etc that go through
these issues.

Or can someone tell me that the posts I have been reading are no longer
applicable and that when I program using Table components that it doesn't
download a complete large dataset and that I only get one row at a time
which takes away the concern, and the need to manage the data scroll
directly. JOIN is not an option because in my project one form can have up
to 9 DETAIL tables showing with the Master table.

I am at the point where I have done a lot of reading and now want to look at
the reality of implementation. Which means I have a little knowledge which
is a dangerous thing. I want to change that status.

Many thanks

Kerry

RE: recommended books for web app.

[Kerry Frater]

Peter,

If you put any in your email reply - none came through.

Kerry


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 15 August 2004 05:21
To: [EMAIL PROTECTED]
Subject: Re: recommended books for web app.






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

RE: recommended books for web app.

[Kerry Frater]

Thanks Mike,

your recommendations are very much noted.

Kerry

-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: 15 August 2004 17:22
To: [EMAIL PROTECTED]
Subject: Re: recommended books for web app.


Kerry,

At 08:09 AM 8/12/2004, you wrote:
I am looking to port an app from an existing web environment to MySQL. The
requirement is relatively easy. The Tables are read only and the data is to
be only accessed via login  password. The login will give a limited view
of
records based on a master/detail table relationship. I need to be aware of
securing the database and have been told by others that I should look to
use
PHP.

I know my local bookstore has the following publications (based on asking
about MySQL  PHP)
Beginning PHP, Apache MySQL Web Development published by Wrox

PHP  MySQL written by Larry Ullmen

I'd recommend this one first (actually he has 2 books on PHP  MySQL, an
intro and an advanced version) because it will get you the basics for using
PHP and MySQL. You can also finish each one in about a week because it is
not that large.


PHP  MySQL Web Development written by Luke Welling  Laura Thomsan

Read this book next because it is quite thorough and will take some time to
get through.

Mike


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

recommended books for web app.

[Kerry Frater]

I am looking to port an app from an existing web environment to MySQL. The
requirement is relatively easy. The Tables are read only and the data is to
be only accessed via login  password. The login will give a limited view of
records based on a master/detail table relationship. I need to be aware of
securing the database and have been told by others that I should look to use
PHP.

I know my local bookstore has the following publications (based on asking
about MySQL  PHP)
Beginning PHP, Apache MySQL Web Development published by Wrox

PHP  MySQL written by Larry Ullmen

PHP  MySQL Web Development written by Luke Welling  Laura Thomsan

Has anyone seen these books and possibly recommend one of them?


Thanks

Kerry

RE: recommended books for web app.

[Kerry Frater]

Thanks for your recommendation Peter

Kerry
  -Original Message-
  From: Peter Brawley [mailto:[EMAIL PROTECTED]
  Sent: 12 August 2004 14:23
  To: Kerry Frater; MySQL List
  Subject: Re: recommended books for web app.


  Welling  Thomson is terrific.
- Original Message -
From: Kerry Frater
To: MySQL List
Sent: Thursday, August 12, 2004 8:09 AM
Subject: recommended books for web app.


I am looking to port an app from an existing web environment to MySQL.
The
requirement is relatively easy. The Tables are read only and the data is
to
be only accessed via login  password. The login will give a limited
view of
records based on a master/detail table relationship. I need to be aware
of
securing the database and have been told by others that I should look to
use
PHP.

I know my local bookstore has the following publications (based on
asking
about MySQL  PHP)
Beginning PHP, Apache MySQL Web Development published by Wrox

PHP  MySQL written by Larry Ullmen

PHP  MySQL Web Development written by Luke Welling  Laura Thomsan

Has anyone seen these books and possibly recommend one of them?


Thanks

Kerry

Creating a New User - What am I doing wrong?

[Kerry Frater]

I am using 4.0.20a Windows version downloaded as the binary file with
Installer built in.
The system was installed with the defaults. Nothing was run and the
default mysqld was allowed to run at start up. I have tried this using
Windows XP, Windows 2000 Pro  Windows 98.

I am testing the system by using the mysql program to insert records from
the PC machine to the machine where the MySQL installation is.

My manual implies that if I created a user ullcopy with a password pllcopy
using the syntax

grant all on llcopy.* to ullcopy identified by 'pllcopy';

then it would create a global user thoat could log on from anywhere BUT
whether I am on the local machine or remote machine or if I use the -h
option then I get an error giving me ACCESS denied

to login using the local machine I had to be specific

grant all on llcopy.* to ullcopy@'localhost' identified by 'pllcopy';

Only then did the mysql starting command

mysql -uullcopy -ppllcopy

actually work.

Secondly,

Take my two PC's
KERRY - 192.168.1.113 (Windows 2000)
SIAN - 192.168.1.115 (Windows 98)

mask - 255.255.255.0

with MySQL installed on machine SIAN. I used the TCP/IP install example on
machine SIAN to set up a new user

grant all on llcopy.* to ullcopy@'192.168.1.%' identified by 'pllcopy';

I would now expect me to be able to login from machine KERRY using the
command

mysql -h192.168.1.115 -uullcopy -ppllcopy

since they are on the same network. But I get an error telling me that
Access is denied to [EMAIL PROTECTED]

The only way I could get me to be able to login was to set up a user

grant all on llcopy.* to ullcopy@'KERRY' identified by 'pllcopy';

Now it worked.

Unless there is a step I am unaware of, this implies that for 4.0.20a I have
to set up a user for every computer name I have on a network i.e. If I have
20 PC's and I want a user ullcopy then I have to create 20 ullcopy users
which cannot be correct.

I also tried
grant all on llcopy.* to ullcopy@'%' identified by 'pllcopy';

but I still got access denied. I also tried it with the MySQL server running
on my Windows 2000 and Windows XP (Pros). All with the same results.

Can someone help me please

Kerry

RE: Creating a New User - What am I doing wrong?

[Kerry Frater]

Many thanks Paul,

I will read and digest tomorrow in normat daylight hours.

Kerry

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: 09 August 2004 20:55
To: Kerry Frater; MySQL List
Subject: Re: Creating a New User - What am I doing wrong?


At 20:25 +0100 8/9/04, Kerry Frater wrote:
I am using 4.0.20a Windows version downloaded as the binary file with
Installer built in.
The system was installed with the defaults. Nothing was run and the
default mysqld was allowed to run at start up. I have tried this using
Windows XP, Windows 2000 Pro  Windows 98.

I am testing the system by using the mysql program to insert records from
the PC machine to the machine where the MySQL installation is.

My manual implies that if I created a user ullcopy with a password pllcopy
using the syntax

grant all on llcopy.* to ullcopy identified by 'pllcopy';

then it would create a global user thoat could log on from anywhere BUT
whether I am on the local machine or remote machine or if I use the -h
option then I get an error giving me ACCESS denied

This problem goes away if you remove the anonymous-user accounts, as
described here:

http://dev.mysql.com/doc/mysql/en/Default_privileges.html

The reason for what you are seeing is explained here:

http://dev.mysql.com/doc/mysql/en/Connection_access.html


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
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]

creating a new user with mysql on 4.0.20a

[Kerry Frater]

I am just going through some taching docs and it says that I can create a
new user with all authority on a new DB using the command

GRANT ALL ON llcopy.* TO auser IDENTIFIED BY 'thepassword';

yet when I try and run 'mysql' using
mysql -uauser -pthepassword

I get the error
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)

Is there an easy way to GRANT priveleges to users no matter where they
logion from?

Kerry

FW: creating a new user with mysql on 4.0.20a

[Kerry Frater]

Oops,
used wrong email address


-Original Message-
Sent: 08 August 2004 12:23
To: [EMAIL PROTECTED]
Subject: creating a new user with mysql on 4.0.20a


I am just going through some taching docs and it says that I can create a
new user with all authority on a new DB using the command

GRANT ALL ON llcopy.* TO auser IDENTIFIED BY 'thepassword';

yet when I try and run 'mysql' using
mysql -uauser -pthepassword

I get the error
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)

Is there an easy way to GRANT priveleges to users no matter where they
logion from?

Kerry

keep losing login rights with MySQL

[Kerry Frater]

I have installed 4.0.20a binary on my own PC to work with MySQL. I take my
PC home with me. The only thing I change at home is my IP address as my home
n/w is different from my work n/w IP range and I have a VPN link between the
office  home.

Since using this version (.17 was there before) everytime I change the IP
addresses of the machine I have difficulty in getting access to the MySQL
server with admin rights. I set a user  password but if I try and login as
root with the password using
mysql -uroot -p

I get the error
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

If I do not use the password with the command
mysql
I can login. It tells me that I have logged in with [EMAIL PROTECTED] but I
cannot see any of the tables that I could before. I also can't use or view
the mysql table.
I tried using the MySQL Administrator program. Again I have to login without
the password.. The window comes up . If I click on User Administration I
get Could not fetch User names MySQL Error Nr 1044 access denied for user
'root@ localhost' to database mysql

The only thing changed (done 3 days ago) was to Service Contol-Configure
Service add Support for InnoDB and Named Pipes which changed the
ImagePath entry from mysqld-nt to mysql-max-nt

Is there anything I can do to check the integrity of the mysql database to
see if it has corrupted? I have not long started looking at this so there
are no backups just my play area. I have a copy on a second machine but I
don't just want to copy directory structures over because I presume I need
to do more than that.

Any help to get my administration rights back gratefully recieved.

Kerry

MySQL Crystal Reports V9

[Kerry Frater]

Anyone know of some decent docs on how CR works with MySQL using ODBC 3.51.

Just installed it today to see what it's like and am finding it cumbersome.
This is most likely due to ignorance rather than the product.

I was expecting to see a list of tables available for reporting when using a
link though instead of having to write SQL statements.

Regards