Access to MySQL query problem

[Ed Reed]

I'm coverting an Access database to MySQL 4.1.1 and I need help with a
near impossible query. In the Access DB I used a stored procedure; I
think I should be able to solve this problem with a subselect in MySQL
but so far I'm not having any luck.
 
Here's the problem; In my stored precedure query I get results that
look like this

Item  OpUser
2751  2 Dude
2751  3 Aguy
4785  1 Dude
4785  2 Gus
5623  1 Dude
5623  2 Gus
5654  1 Gus
5654  2 Aguy
 
I then query these results to get only the Items for a user when the
specified user is the first person for that Item ID, for example. If I
query the results for Dude Items 2751, 4785 & 5623 are returned. If I
query the results for Gus Item 5654 are return but not Items 4785 or
5623. If I query the results for Aguy no Items are returned. 
 
Does anyone have any thoughts on this?
 
Thanks

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

Re: Limiting the data returned to just what I need

[Peter Frank Spierenburg]

On Sun, 28 Mar 2004, Scott Haneda wrote:

> Client wants a "hitlist" that shows the user data and the associated serial
> numbers for a list of users.  If the query returns 20 serial numbers, I am
> going to get back 19 name, address, state, zip etc rows that I don't need, I
> can easily discard them, but this seems less than efficient.

How do you identify the one set of values that you need?

Peter.

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

Limiting the data returned to just what I need

[Scott Haneda]

Wonder if there is a more efficient way of dealing with the following...

I have a user table and a serial_numbers table

User table has name, address, state, zip etc
Serial_numbers table has a id and a serial number.

Client wants a "hitlist" that shows the user data and the associated serial
numbers for a list of users.  If the query returns 20 serial numbers, I am
going to get back 19 name, address, state, zip etc rows that I don't need, I
can easily discard them, but this seems less than efficient.

-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.


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

Re: to_days() error?

[Paul DuBois]

At 16:23 -0600 3/28/04, unruhtech wrote:
the following works:
SELECT to_days( curdate() + 0 ) , to_days( curdate() + 3 ) FROM table
732033 732036
That's correct.

but this does not:
SELECT to_days( curdate() + 0 ) , to_days( curdate() + 4 ) FROM table
732033 NULL
That's correct.  CURDATE() + 4 is the number 20040332, which can't
be interpreted as a date by TO_DAYS() -- 32 isn't a valid day in
any month.
You could use this expression instead:

TO_DAYS(CURDATE() + INTERVAL 4 DAY);

Or just TO_DAYS(CURDATE()) + 4.

an end of month problem or a curdate() problem or what?

curdate( ) + 0,  curdate( ) + 7 
20040328 20040335

no month i know of has 35 days in it.
You're right.  But the *number* 20040328 plus 7 *does* equal 20040335.

You're thinking that the value of CURDATE() when used in a numeric
context is actually a date, right?  It's a number.
3.23.58 on red hat 7.3

Steve

p.s. any tips for a query on today and today +7 days?
TO_DAYS(CURDATE()) + 7 rather than TO_DAYS(CURDATE() + 7)
will probably give you results more like what you're expecting.
Or TO_DAYS(CURDATE() + INTERVAL 7 DAY).
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: need reference for a good book

[W. D.]

At 13:41 3/28/2004, A Mathias wrote:
>Preferably one that is for begginers to medium and thats covers both mySQL 
>and PHP
>
>Thanks

I like the Welling & Thomson book:
http://tinyurl.com/3cpk5

Start Here to Find It Fast!™ -> http://www.US-Webmasters.com/best-start-page/


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

Re: to_days() error?

[Daniel Kasak]

unruhtech wrote:

the following works:
SELECT to_days( curdate() + 0 ) , to_days( curdate() + 3 ) FROM table
732033 732036
but this does not:
SELECT to_days( curdate() + 0 ) , to_days( curdate() + 4 ) FROM table
732033 NULL 

an end of month problem or a curdate() problem or what?

curdate( ) + 0,  curdate( ) + 7  
20040328 20040335 

no month i know of has 35 days in it.

3.23.58 on red hat 7.3

Steve

p.s. any tips for a query on today and today +7 days?

 

You can't simply go:

curdate() + x

like you are trying to, as curdate() returns an integer, and your '+ x' 
adds one to it. Use a date addition function.

Dan

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table 'LIKE' ....Error 1064

[Paul DuBois]

At 7:48 -0800 3/28/04, Khashan wrote:
Hello All,
I have MySQL 4.0.16 on Windows 2000 pro.
I am trying to create a table in an existing database.
according to the manual, I can use one of these statements:
6.5.3 CREATE TABLE Syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
or
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];
I am trying to use the second option. I have a table students and i 
want to create another table students2 using the 'LIKE'  students. 
Here are the results:

mysql> CREATE TABLE students2 LIKE students;
ERROR 1064: 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 'LIKE students' at line 1
mysql> CREATE TABLE students2 (LIKE students);
ERROR 1064: 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 'LIKE students)' at line 1

1064 is ER_PARSE_ERROR
I tried all the tricks i know to no avail. I really don't see the 
error in this simple command,!!!.   Any help would be appricated.
Farther down the manual page:

In MySQL 4.1, you can also use LIKE to create a table based on the 
definition of another table, including any column attributes and 
indexes the original table has:
CREATE TABLE new_tbl LIKE orig_tbl;

So you need 4.1 for this to work.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

to_days() error?

[unruhtech]

the following works:
SELECT to_days( curdate() + 0 ) , to_days( curdate() + 3 ) FROM table
732033 732036

but this does not:
SELECT to_days( curdate() + 0 ) , to_days( curdate() + 4 ) FROM table
732033 NULL 

an end of month problem or a curdate() problem or what?

curdate( ) + 0,  curdate( ) + 7  
20040328 20040335 

no month i know of has 35 days in it.

3.23.58 on red hat 7.3

Steve

p.s. any tips for a query on today and today +7 days?

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

Create table 'LIKE' ....Error 1064

[Khashan]

Hello All,
I have MySQL 4.0.16 on Windows 2000 pro.
I am trying to create a table in an existing database.
according to the manual, I can use one of these statements:
6.5.3 CREATE TABLE Syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
or
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];
 
I am trying to use the second option. I have a table students and i want to create 
another table students2 using the 'LIKE'  students. Here are the results:
 
mysql> CREATE TABLE students2 LIKE students;
ERROR 1064: 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 'LIKE students' at line 1
mysql> CREATE TABLE students2 (LIKE students);
ERROR 1064: 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 'LIKE students)' at line 
1
 
1064 is ER_PARSE_ERROR 
I tried all the tricks i know to no avail. I really don't see the error in this simple 
command,!!!.   Any help would be appricated. 

Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam

need reference for a good book

[A Mathias]

Preferably one that is for begginers to medium and thats covers both mySQL and PHP

Thanks

QMySQLBackup-Tool

[Christian Kienle]

Hello,

I have written a little backup tool for mysql databases with a graphical user 
interface. It is written in Qt and only supports very basic features. 
Perhaps some of you would like to help makeing it better.
It is open source - written with Qt - so it is available on Windows, Linux, 
Unix and MacOSX.

You can download it from:
http://www.qtforum.org/members/christian/QMySQLBackup.tar.gz

Under Linux you can compile it with:
qmake -o Makefile *.pro && make

Have fun...

greets Christian

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

RE: Getting count(*) with LIMIT in SQL

[Chris]

In 4+ you can use SQL_CALC_FOUND_ROWS flag

http://www.mysql.com/doc/en/SELECT.html

-Original Message-
From: Steffan A. Cline [mailto:[EMAIL PROTECTED]
Sent: Sunday, March 28, 2004 8:47 AM
To: MySql
Subject: Getting count(*) with LIMIT in SQL


I have been tinkering with finding the fastest way to do my searches and
return less info for faster performance. In a project I have, I build the
sql query with information from a form page. The query may look like this:

SELECT * FROM masterlist WHERE market LIKE "%%" AND source LIKE "%%" AND
clientstatus LIKE "%%" AND ( client LIKE "%%" OR contact LIKE "%%" ) ORDER
BY client LIMIT 0,1 ;

Ignore the LIKE "%%". This is caused when fields are selected to search on
and no keywords are found. I'll fix that later. This works fine and dandy
except I need to get the overall found count as well. I was toying with
COUNT(*) to see if I could get the found count returned too. If I do this :

SELECT *, COUNT(*) AS found  FROM masterlist WHERE market LIKE "%%" AND
source LIKE "%%" AND clientstatus LIKE "%%" AND ( client LIKE "%%" OR
contact LIKE "%%" ) ORDER BY client LIMIT 0,1 ;

I get the following error :

Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is
illegal if there is no GROUP BY clause (1140)

If I do the following :

SELECT COUNT(*) AS found  FROM masterlist WHERE market LIKE "%%" AND source
LIKE "%%" AND clientstatus LIKE "%%" AND ( client LIKE "%%" OR contact LIKE
"%%" ) ORDER BY client LIMIT 0,1 ;

I get the response of found 8214. This is correct!

Isn't there some way to get the found count of the query returned with the
results of the search? The goal is NOT to have 2 searches. One that gives
the found count and then one that actually returns the data.

BTW, Is there any MAJOR dent in performance if somehow I end up with a
search where... column LIKE "%%"

SQL Gurus! Any way to do this?


Thanks

Steffan

---
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net  USA
AIM : SteffanC  ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
  Lasso Partner Alliance Member
---



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

Getting count(*) with LIMIT in SQL

[Steffan A. Cline]

I have been tinkering with finding the fastest way to do my searches and
return less info for faster performance. In a project I have, I build the
sql query with information from a form page. The query may look like this:

SELECT * FROM masterlist WHERE market LIKE "%%" AND source LIKE "%%" AND
clientstatus LIKE "%%" AND ( client LIKE "%%" OR contact LIKE "%%" ) ORDER
BY client LIMIT 0,1 ;

Ignore the LIKE "%%". This is caused when fields are selected to search on
and no keywords are found. I'll fix that later. This works fine and dandy
except I need to get the overall found count as well. I was toying with
COUNT(*) to see if I could get the found count returned too. If I do this :

SELECT *, COUNT(*) AS found  FROM masterlist WHERE market LIKE "%%" AND
source LIKE "%%" AND clientstatus LIKE "%%" AND ( client LIKE "%%" OR
contact LIKE "%%" ) ORDER BY client LIMIT 0,1 ;

I get the following error :

Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is
illegal if there is no GROUP BY clause (1140)

If I do the following :

SELECT COUNT(*) AS found  FROM masterlist WHERE market LIKE "%%" AND source
LIKE "%%" AND clientstatus LIKE "%%" AND ( client LIKE "%%" OR contact LIKE
"%%" ) ORDER BY client LIMIT 0,1 ;

I get the response of found 8214. This is correct!

Isn't there some way to get the found count of the query returned with the
results of the search? The goal is NOT to have 2 searches. One that gives
the found count and then one that actually returns the data.

BTW, Is there any MAJOR dent in performance if somehow I end up with a
search where... column LIKE "%%"

SQL Gurus! Any way to do this?


Thanks

Steffan

---
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net  USA
AIM : SteffanC  ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
  Lasso Partner Alliance Member
---



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

Re: Bug in MySQL with Correlated Subqueries?

[Michael Stassen]

Are you saying this is a bug which has already been fixed for the next release?

In any case, it may be worth noting that the manual says correlated 
subqueries "are inefficient and likely to be slow. Rewriting the query as a 
join might improve performance." 
.

  SELECT person.pid, count(*) AS C
  FROM person, phone
  WHERE person.pid=phone.pid
  GROUP BY person.pid;
  +--+---+
  | pid  | C |
  +--+---+
  |1 | 2 |
  |2 | 1 |
  +--+---+
  2 rows in set (0.00 sec)
Michael

Miguel Angel Solorzano wrote:
At 13:59 27/3/2004, Ed Smith wrote:
Hi,
Below the results from a server built with BK 4.1 tree 3
days ago:

>
Miguel Angel Solórzano <[EMAIL PROTECTED]>
São Paulo - Brazil


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

Re: 6.5 seconds seems slow for the following search?

[Kyle Renfro]

On Sat, 2004-03-27 at 13:59, Dan Nelson wrote:
> In the last episode (Mar 26), Kyle Renfro said:
> > I am testing MySQL as a possible replacement for our proprietary db
> > server for several large databases.  I really want MySQL but I am
> > getting what seems like slow search times.
> > 
> > Does 6.5 seconds seem slow/typical/fast for the following search?
> > 
> > SELECT main.ownername FROM main, rolledplate WHERE
rolledplate.platenum
> > LIKE '3^6SP%' AND main.recid = rolledplate.recid;
> > 
> > The 'main' table has 21+ million records.  The 'rolledplate' table
has
> > 144+ million records.
> >
> > The EXPLAIN gives pretty optimal results and I have tried the select
> > syntax several different ways. The recid field is the PK in the main
> > table.  In both tables RECID is an unsigned int with a 1:M
> > relationship between main and rolledplate.  The tables are MyISAM
> > with a fixed row format.
> 
> What's the EXPLAIN look like, and how many records do you get from the
> above query: total, and average per rollplate.recid (i.e what's M)?  6
> seconds could be high if you are returning only a couple of records,
> but if you're returning over 600 records, than it's reasonable
> (assuming slow disks at 100 seeks/sec).  Since you're only fetching
one
> field, creating multicolumn indexes on rolledplate (platenum, recid)
> and main (recid, ownername) may let you avoid table lookups
altogether.
> 
> -- 
>   Dan Nelson
>   [EMAIL PROTECTED]

Here is EXPLAIN:

++-+-++---+-+-+---+--+-+
| id | select_type | table   | type   | possible_keys |
key | key_len | ref   | rows | Extra   |
++-+-++---+-+-+---+--+-+
|  1 | SIMPLE  | rolledplate | range  | IDX_RECID,IDX_PLATENO |
IDX_PLATENO |   8 | NULL  |  131 | Using where |
|  1 | SIMPLE  | main| eq_ref | PRIMARY,IDX_RECID |
PRIMARY |   4 | mvr.rolledplate.RECID |1 | |
++-+-++---+-+-+---+--+-+
2 rows in set (0.00 sec)


or more readable version:
typetabletype   key klen  ref   rows  Extra
--
simple  rolledplate  range  IDX_PLATENO 8 NULL  131   using where   
simple  main eq_ref PRIMARY 4 RECID 1


The query is returning 164 records. M is about 7. The multi-column index
is a good idea but in general I will need several more fields.  I just
used one for this example.




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

Re: Multiple-table Update

[Nitin Mehta]

which ver of mysql are you using? multiple table update is possible only
with ver starting from 4.0.4.

HTH..
Nitin


- Original Message - 
From: "Laercio Xisto Braga Cavalcanti" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, March 26, 2004 11:39 PM
Subject: Multiple-table Update


> 'm using Multiple-table Update with the above sintax:
> mysql> desc italica;
> ++---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> ++---+--+-+-+---+
> | ita_empresa | decimal(10,0) | | | 0 | |
> | ita_matricula | decimal(10,0) | | PRI | 0 | |
> | ita_dv | decimal(1,0) | | PRI | 0 | |
> | ita_nome | varchar(50) | YES | | NULL | |
> | ita_fone | varchar(19) | YES | | NULL | |
> | ita_nascimento | date | YES | | NULL | |
> | ita_adesao | date | YES | | NULL | |
> | ita_ultpag | date | YES | | NULL | |
> ++---+--+-+-+---+
> 8 rows in set (0.00 sec)
> mysql> desc paciente
> -> ;
>
+-+---+--+-++---
-+
> | Field | Type | Null | Key | Default | Extra
> |
>
+-+---+--+-++---
-+
> | pac_codigo | int(11) | | PRI | NULL |
> auto_increment |
> | pac_nome | varchar(100) | YES | | NULL |
> |
> | pac_rg | int(11) | | | 0 |
> |
> | pac_nascimento | date | | | -00-00 |
> |
> | pac_sexo | enum('M','F') | | | M |
> |
> | pac_endereco | varchar(50) | | | |
> |
> | pac_numero | int(11) | | | 0 |
> |
> | pac_complemento | varchar(50) | YES | | NULL |
> |
> | pac_celular | varchar(9) | YES | | NULL |
> |
> | pac_fone | varchar(9) | YES | | NULL |
> |
> | pac_fone_com | varchar(9) | YES | | NULL |
> |
> | pac_convenio | enum('S','N') | | | S |
> |
> | pac_matricula | int(11) | YES | | NULL |
> |
> | pac_adesao | date | YES | | NULL |
> |
> | pac_email | varchar(50) | YES | | NULL |
> |
> | pac_dv | int(1) | YES | | NULL |
> |
> | pac_bairro | varchar(50) | YES | | NULL |
> |
> | pac_cep | varchar(10) | YES | | NULL |
> |
> | pac_cidade | varchar(50) | YES | | NULL |
> |
> | pac_ultimopag | date | YES | | NULL |
> |
>
+-+---+--+-++---
-+
> 20 rows in set (0.00 sec)
>
> update paciente, italica
> SET pac_nome = ita_nome,
> pac_fone = ita_fone,
> pac_nascimento = ita_nascimento,
> pac_ultimopag = ita_ultpag
> WHERE ita_matricula = pac_matricula
> AND ita_dv = pac_dv;
>
> and I got the following error:
>
> ERROR 1064: You have an error in your SQL syntax near ' italica
> SET pac_nome = ita_nome,
> pac_f' at line 1
>
>
> What can I do?
>
>
>
>
> -- 
> 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]

Query-cache questions

[Mark]

Hello,

I just upgraded to MySQL 4.0.18. I have a question about the new
query-cache. The documentation says:

"The FLUSH TABLES statement also flushes the query cache."

What does that mean exactly? Does it mean FLUSH TABLES, in effect, is equal
to RESET QUERY CACHE, in that it purges the cache? Or does it mean FLUSH
TABLES just defrags the query-cache, and has the effect of FLUSH QUERY
CACHE?

I would also be interesting to know whether defragging the query-cache takes
any disk I/O.

Also, I initially set the query_cache_size to 128M. That is a bit excessive,
perhaps. Is there a known, say, 'key_buffer to query_cache_size' ratio?

Thanks,

- Mark



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