Re: how to query this
This can also help: select id,value from table order by value desc limit 1; Cheers Claudio 2009/9/10 Slackli User > Thanks all the info. > Just got what I wanted. > > 2009/9/10 Wolfgang Schaefer : > > John Daisley schrieb: > >> SELECT MAX(value), id FROM table > >> GROUP BY id; > >> > >> > >> > > > > I guess what Slackli had in mind was more something like this: > > SELECT id, value > > FROM table > > WHERE value = (SELECT max(value) FROM table); > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > -- Claudio
Re: how to query this
Thanks all the info. Just got what I wanted. 2009/9/10 Wolfgang Schaefer : > John Daisley schrieb: >> SELECT MAX(value), id FROM table >> GROUP BY id; >> >> >> > > I guess what Slackli had in mind was more something like this: > SELECT id, value > FROM table > WHERE value = (SELECT max(value) FROM table); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to query this
John Daisley schrieb: > SELECT MAX(value), id FROM table > GROUP BY id; > > Regards > > John Daisley > Mobile +44(0)7812 451238 > Email j...@butterflysystems.co.uk > > Certified MySQL 5 Database Administrator (CMDBA) > Certified MySQL 5 Developer > Cognos BI Developer > > --- > Sent from HP IPAQ mobile device. > > I guess what Slackli had in mind was more something like this: SELECT id, value FROM table WHERE value = (SELECT max(value) FROM table); cheers, wolfgang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how to query this
SELECT MAX(value), id FROM table GROUP BY id; Regards John Daisley Mobile +44(0)7812 451238 Email j...@butterflysystems.co.uk Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer Cognos BI Developer --- Sent from HP IPAQ mobile device. -Original Message- From: Slackli User Sent: Thursday, September 10, 2009 10:43 AM To: mysql@lists.mysql.com Subject: how to query this Hello, sorry I'm not good at SQL statement. I have a table, whose stru is like: idvalue 1 33 2 987 3 10 4 22 ... I want to get the max value and the corresponding id, using this sql: select max(value),id from table; but it won't work. so what's the correct syntax? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to query this
Hello, sorry I'm not good at SQL statement. I have a table, whose stru is like: idvalue 1 33 2 987 3 10 4 22 ... I want to get the max value and the corresponding id, using this sql: select max(value),id from table; but it won't work. so what's the correct syntax? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
HOW TO QUERY(SELECT) and display MULTIPLE AUTHORS
"SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC "
the structure is rather simple:
book:id = primary key, field1 field8
author: id = primary key, first_name, last_name, ordinal
publishers: id = primary key, publisher
book_author:
authID = primary key, references author.id
bookID = primary key, references book.id
book_publisher:
bookID = primary key, references book.id
publishers_id = primary, key references publishers.id
Question 1 is: How to set up a query for a book with 2 authors?
There are a number of problems involved, but such issues as more than 2
authors, editors and authors in a dictionary of anthology or some
compilation can by revealed in the description field. So we come down to
the problem of just plain 2 authors: do we list the same book twice with
a different author in each instance with the ordinal field showing 1 for
the display priority of the first author and 2 for the second?
Question 2 is: How to retrieve and display the two authors on one line
as Author_1 "and" Author_2 without some incredible conditional
contortions to achieve the goal?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to Query by First Part of a VARCHAR?
the thing to remember is that if you only want strings that start with CAT you'd never want to query with %CAT% because this could match DOG_CATHY. % is a wildcard that matches any number of characters including none. if you want to match a single character you use _ if you actually need to search for _ or % you can escape them with a backslash \% or \_ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Query by First Part of a VARCHAR?
On 7/4/07, Dan Nelson <[EMAIL PROTECTED]> wrote: In the last episode (Jul 04), David T. Ashley said: > On 7/4/07, gary <[EMAIL PROTECTED]> wrote: > > SELECT column FROM table WHERE column LIKE "CAT\_%"; > > Would it be reasonable to assume that if "column" is indexed, the > query would execute quickly, i.e. I would assume that the indexing > would facilitate this kind of query? Yes, but only for prefix checks like in this example. ` LIKE "%CAT%" ' or ` LIKE "%CAT" ' can't use an index. Thanks. I was able to confirm the behavior by creating a table with three identical varchars, populating them randomly with a string of 6 digits but setting each varchar within a row the same, and executing queries. s3 is indexed (below). "LIKE CAT%" was obscenely fast on an indexed column. "LIKE %CAT%" was obscenely slow. Thanks for the help. - mysql> explain stest; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | bigint(20) | | PRI | 0 | | | s1| varchar(200) | YES | | NULL| | | s2| varchar(200) | YES | | NULL| | | s3| varchar(200) | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) mysql> select count(*) from stest; +--+ | count(*) | +--+ | 1480131 | +--+ 1 row in set (0.00 sec) mysql> select * from stest where s1="123455"; +++++ | a | s1 | s2 | s3 | +++++ | 246823 | 123455 | 123455 | 123455 | +++++ 1 row in set (2.16 sec) mysql> select * from stest where s3="123455"; +++++ | a | s1 | s2 | s3 | +++++ | 246823 | 123455 | 123455 | 123455 | +++++ 1 row in set (0.00 sec) mysql> select count(*) from stest where s1 like "%"; +--+ | count(*) | +--+ | 136 | +--+ 1 row in set (2.10 sec) mysql> select count(*) from stest where s3 like "%"; +--+ | count(*) | +--+ | 136 | +--+ 1 row in set (0.00 sec) mysql> select count(*) from stest where s1 like "%000%"; +--+ | count(*) | +--+ | 5585 | +--+ 1 row in set (2.19 sec) mysql> select count(*) from stest where s3 like "%000%"; +--+ | count(*) | +--+ | 5585 | +--+ 1 row in set (2.78 sec)
Re: How to Query by First Part of a VARCHAR?
In the last episode (Jul 04), David T. Ashley said: > On 7/4/07, gary <[EMAIL PROTECTED]> wrote: > > SELECT column FROM table WHERE column LIKE "CAT\_%"; > > Would it be reasonable to assume that if "column" is indexed, the > query would execute quickly, i.e. I would assume that the indexing > would facilitate this kind of query? Yes, but only for prefix checks like in this example. ` LIKE "%CAT%" ' or ` LIKE "%CAT" ' can't use an index. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Query by First Part of a VARCHAR?
On 7/4/07, gary <[EMAIL PROTECTED]> wrote: SELECT column FROM table WHERE column LIKE "CAT\_%"; Would it be reasonable to assume that if "column" is indexed, the query would execute quickly, i.e. I would assume that the indexing would facilitate this kind of query?
Re: How to Query by First Part of a VARCHAR?
SELECT column FROM table WHERE column LIKE "CAT\_%"; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Query by First Part of a VARCHAR?
If I have a table with rows like this, all varchar: DOG_LUCY DOG_CHARLIE DOG_LASSIE DOG_XRAY CAT_MR_BIGGLESWORTH CAT_SCRATCHER CAT_WHISTLER what is the form of a query that will return the rows where the first part of the string matches? For example, what if I'd like to return the rows that begin with "CAT_", which should give 3 rows? Thanks. P.S.--This example is contrived to illustrate what I'm trying to achieve. My actual application is different. A database of cats and dogs would naturally be best structured differently. Thanks.
Re: how to query this sql?
On Sun, April 29, 2007 05:28, Jeff Pang wrote: > Hello list, > > I want to get the counter for db-items by each day,so I wrote this sql: > > select count(*) as dd from items group by updatetime; > > But sorry "updatetime" is "datetime" type,not "date" type.Then I can't get > the correct result. > > How can I do this?Thanks. > That would be select date(updatetime) ut, count(*) dd from items group by ut; -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to query this sql?
Hello list, I want to get the counter for db-items by each day,so I wrote this sql: select count(*) as dd from items group by updatetime; But sorry "updatetime" is "datetime" type,not "date" type.Then I can't get the correct result. How can I do this?Thanks. 50 AMAZON-Einkaufsgutschein bei Bestellung von Arcor-DSL: Viel oder wenig? Schnell oder langsam? Unbegrenzt surfen + telefonieren ohne Zeit- und Volumenbegrenzung? DAS TOP ANGEBOT JETZT bei Arcor: günstig und schnell mit DSL - das All-Inclusive-Paket für clevere Doppel-Sparer, nur 39,85 inkl. DSL- und ISDN-Grundgebühr! http://www.arcor.de/rd/emf-dsl-2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to query bind address?
Yes, it's running on Windows XP, but the Windows Firewall has been
disabled.
Just figured it out... This was a version of MySQL bundled with an
application. I wrongly assumed that shutting down the application also
shut down the included MySQL server. The original my.cnf had a
bind-address of 127.0.0.1, which I'd removed, but since the MySQL server
wasn't actually being restarted, the new configuration was never picked
up.
- Original Message -
From: "Dan Buettner" <[EMAIL PROTECTED]>
To: "James Eaton" <[EMAIL PROTECTED]>
Cc:
Sent: Wednesday, August 23, 2006 3:07 PM
Subject: Re: Re: How to query bind address?
Is it running on Windows, James? You may have to allow connections
through to MySQL in Windows Firewall.
Otherwise it seems like maybe it's been started with --skip-networking ?
Dan
On 8/23/06, James Eaton <[EMAIL PROTECTED]> wrote:
- Original Message -
From: "Dan Buettner" <[EMAIL PROTECTED]>
>I believe MySQL listens on all IPs ... what if any error message are
> you getting?
From SQLyog I get:
Error No. 2003
Can't connect to MySQL server on '192.168.9.2' (10061)
> Can you access other services (SSH, telnet, FTP) over the same
> routes?
Yes.
> Can you telnet to MySQL over those routes? If so you may have an
> authentication problem, not a connection problem. Here's a sample of
> what a telnet session might look like:
>
> % telnet 127.0.0.1 3306
> Trying 127.0.0.1...
> Connected to localhost.
> Escape character is '^]'.
> A
> 5.0.21-standard-log&6{-kS?!,$2lMx:Ty%I!d
>
> (I then pressed return and the connection closed)
I can telnet to MySQL from the localhost, but not from any other
location.
> On 8/23/06, James Eaton <[EMAIL PROTECTED]> wrote:
>> Is there a way to query a MySQL (5.0.xx) server to find out which IP
>> address(es) it's listening on? It should be listening on all, but
>> I'm
>> unable to connect from remote machines, so need to troubleshoot a
>> bit.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to query bind address?
Is it running on Windows, James? You may have to allow connections
through to MySQL in Windows Firewall.
Otherwise it seems like maybe it's been started with --skip-networking ?
Dan
On 8/23/06, James Eaton <[EMAIL PROTECTED]> wrote:
- Original Message -
From: "Dan Buettner" <[EMAIL PROTECTED]>
>I believe MySQL listens on all IPs ... what if any error message are
> you getting?
From SQLyog I get:
Error No. 2003
Can't connect to MySQL server on '192.168.9.2' (10061)
> Can you access other services (SSH, telnet, FTP) over the same routes?
Yes.
> Can you telnet to MySQL over those routes? If so you may have an
> authentication problem, not a connection problem. Here's a sample of
> what a telnet session might look like:
>
> % telnet 127.0.0.1 3306
> Trying 127.0.0.1...
> Connected to localhost.
> Escape character is '^]'.
> A
> 5.0.21-standard-log&6{-kS?!,$2lMx:Ty%I!d
>
> (I then pressed return and the connection closed)
I can telnet to MySQL from the localhost, but not from any other location.
> On 8/23/06, James Eaton <[EMAIL PROTECTED]> wrote:
>> Is there a way to query a MySQL (5.0.xx) server to find out which IP
>> address(es) it's listening on? It should be listening on all, but I'm
>> unable to connect from remote machines, so need to troubleshoot a bit.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query bind address?
- Original Message -
From: "Dan Buettner" <[EMAIL PROTECTED]>
I believe MySQL listens on all IPs ... what if any error message are
you getting?
From SQLyog I get:
Error No. 2003
Can't connect to MySQL server on '192.168.9.2' (10061)
Can you access other services (SSH, telnet, FTP) over the same routes?
Yes.
Can you telnet to MySQL over those routes? If so you may have an
authentication problem, not a connection problem. Here's a sample of
what a telnet session might look like:
% telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
A
5.0.21-standard-log&6{-kS?!,$2lMx:Ty%I!d
(I then pressed return and the connection closed)
I can telnet to MySQL from the localhost, but not from any other location.
On 8/23/06, James Eaton <[EMAIL PROTECTED]> wrote:
Is there a way to query a MySQL (5.0.xx) server to find out which IP
address(es) it's listening on? It should be listening on all, but I'm
unable to connect from remote machines, so need to troubleshoot a bit.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query bind address?
I believe MySQL listens on all IPs ... what if any error message are
you getting?
Can you access other services (SSH, telnet, FTP) over the same routes?
Can you telnet to MySQL over those routes? If so you may have an
authentication problem, not a connection problem. Here's a sample of
what a telnet session might look like:
% telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
A
5.0.21-standard-log&6{-kS?!,$2lMx:Ty%I!d
(I then pressed return and the connection closed)
Dan
On 8/23/06, James Eaton <[EMAIL PROTECTED]> wrote:
Is there a way to query a MySQL (5.0.xx) server to find out which IP
address(es) it's listening on? It should be listening on all, but I'm
unable to connect from remote machines, so need to troubleshoot a bit.
--
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]
How to query bind address?
Is there a way to query a MySQL (5.0.xx) server to find out which IP address(es) it's listening on? It should be listening on all, but I'm unable to connect from remote machines, so need to troubleshoot a bit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
At 06:20 PM 7/20/2006, Martin Jespersen wrote:
As long as backticks are used around fieldnames, spaces and/or reserved
words are fine, tho it does tend to create more work for the user ;)
Correct. But how many people want to create more work for themselves? A
show of hands please!
Mike
mos wrote:
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious
tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a
SELECT, but can't seem to use it in a WHERE clause at all. There has to
be something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character "_".
You are only creating problems for yourself if you leave the blanks in
the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
As long as backticks are used around fieldnames, spaces and/or reserved
words are fine, tho it does tend to create more work for the user ;)
mos wrote:
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each
person has a registration date, kept in a standard date field. How do
I select for people who registered in a particular month or year? The
obvious tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a
SELECT, but can't seem to use it in a WHERE clause at all. There has
to be something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character "_".
You are only creating problems for yourself if you leave the blanks in
the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious
tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character "_". You
are only creating problems for yourself if you leave the blanks in the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
Barry, I think you've got too many quotes in your SQL - the db is
trying to find the year from the string 'date paid'. You want to use
it as a column name, so drop the quotes:
Select * from Capclave2005reg
Where Year(Date Paid) = 2004;
If you've really got a space in your column name, try enclosing it in
backticks instead of quotes - ` instead of ', as in
Select * from Capclave2005reg
Where Year(`Date Paid`) = 2004;
Regards,
Dan
On 7/20/06, Barry Newton <[EMAIL PROTECTED]> wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious tests
like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
--
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]
How to query on part of a date column?
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious tests
like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
"high priority" messages (Re: How to query an oracle table from a mysql database)
Hi all, Please, do NOT send messages to a public mailinglist as "high priority" by flagging the email itself. You're sending this message to hundreds/thousands of people. It might be "important" to you, but not for each recipient. If someone has filters/triggers for "important" email, he will get quite annoyed to find out it's only an emailinglist message. People WILL read and respond to your message with the "high priority" flag. With regards, Martijn Tonies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to query an oracle table from a mysql database
Hello Victor, For your off-topic question, my problem is the same as the one described here: http://bugs.php.net/bug.php?id=28680 I can't access using 9.2. They say 9.2 is too new for current PHP versions. I'll install an older version of the Oracle client and try again. If I can't do it I'll be asking for additional ideas :) (I'm pretty comforable with C but I'm not sure what a MySQL UDF is, I've got to find it out). Thanks a lot, --Claudio -Mensaje original- De: Victor Pendleton [mailto:[EMAIL PROTECTED] Enviado el: miércoles 7 de julio de 2004 15:46 Para: 'Alonso, Claudio Fabian '; ''Steve Davies' ' CC: '''[EMAIL PROTECTED]' ' ' Asunto: RE: How to query an oracle table from a mysql database A custom MySQL UDF is one solution if you are comfortable with C. Off list question: What sort of `problem` does PHP have with Oracle 9i that is prohibiting you? -Original Message- From: Alonso, Claudio Fabian To: 'Steve Davies' Cc: ''[EMAIL PROTECTED]' ' Sent: 7/7/04 11:18 AM Subject: RE: How to query an oracle table from a mysql database Importance: High Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to "see" the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. --Claudio .. -- 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: How to query an oracle table from a mysql database
A custom MySQL UDF is one solution if you are comfortable with C. Off list question: What sort of `problem` does PHP have with Oracle 9i that is prohibiting you? -Original Message- From: Alonso, Claudio Fabian To: 'Steve Davies' Cc: ''[EMAIL PROTECTED]' ' Sent: 7/7/04 11:18 AM Subject: RE: How to query an oracle table from a mysql database Importance: High Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to "see" the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. --Claudio .. -- 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: How to query an oracle table from a mysql database
At 13:18 -0300 7/7/04, Alonso, Claudio Fabian wrote: Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to "see" the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. It's not. You must use a program as a bridge, as people have been suggesting. -- 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]
RE: How to query an oracle table from a mysql database
No, that isn't possible using mySQL. Try linking PHP with older client libraries (9.0.1, 8.1.5, etc) instead of the newer 9.2 libraries and see if that fixes your problem with PHP. You can download them from otn.oracle.com swany --- "Alonso, Claudio Fabian" <[EMAIL PROTECTED]> wrote: > Hello Steve, hello Victor, > Thanks for your answers. > My PHP application needs to see this Oracle table, > but as far as I could see > PHP has problems with Oracle 9.2.0. I got a > conection problem and found in > the PHP's bug database that it isn't currently > working. > As I'm familiar with PHP/MySQL, I'm trying to "see" > the Oracle table through > MySQL. > That's why I'm thinking on a way to create in MySQL > a view (or something > similar) that refers to an external database table > (in this case, Oracle). > I don't know if this kind of solution is possible, > using only MySQL to see a > remote Oracle table, not including any programming > language. > > --Claudio > .. > > -- > 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: How to query an oracle table from a mysql database
Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to "see" the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query an oracle table from a mysql database
Steve et al: If you want to do it right select a programming language such as Java or C++ There are enough good OOA/OOD folks out there that you will be able to pick a good Java or C++ Engineer for the same price as PHP Otherwise you will be adding on Disk Storage when your single threaded app starts exhausting memory (including virtual disk drive space in particular) and computer resources in general Keep me apprised, Martin Gainty (cell) 617-852-7822 (e) [EMAIL PROTECTED] (http)www.laconiadatasystems.com From: Steve Davies <[EMAIL PROTECTED]> To: "'Alonso, Claudio Fabian '" <[EMAIL PROTECTED]> CC: "''[EMAIL PROTECTED]' '" <[EMAIL PROTECTED]> Subject: Re: How to query an oracle table from a mysql database Date: Wed, 07 Jul 2004 17:02:59 +0100 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc6-f16.hotmail.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 7 Jul 2004 09:03:56 -0700 Received: (qmail 31976 invoked by uid 109); 7 Jul 2004 16:01:08 - Received: (qmail 31955 invoked from network); 7 Jul 2004 16:01:08 - Received: neutral (lists.mysql.com: local policy) X-Message-Info: JGTYoYF78jHJy6039BA9f6fM4wGqMq15 Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: Precedence: bulk List-Help: <mailto:[EMAIL PROTECTED]> List-Unsubscribe: <mailto:[EMAIL PROTECTED]> List-Post: <mailto:[EMAIL PROTECTED]> List-Archive: http://lists.mysql.com/mysql/168573 Delivered-To: mailing list [EMAIL PROTECTED] Message-ID: <[EMAIL PROTECTED]> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en References: <[EMAIL PROTECTED]> In-Reply-To: <[EMAIL PROTECTED]> X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 07 Jul 2004 16:04:00.0836 (UTC) FILETIME=[046E2840:01C4643C] Try PHP, it has built in functions to access both DBs HTH Steve Victor Pendleton wrote: You may want to look into using a programming language such as C or Java that communicates between both databases. -Original Message- From: Alonso, Claudio Fabian To: '[EMAIL PROTECTED]' Sent: 7/7/04 8:14 AM Subject: How to query an oracle table from a mysql database Importance: High Good morning, I need a MySQL database to be able to query a table in an Oracle database of a remote host. Is this possible? Could anybody tell me how to do it or where to find this information? Thanks in advance, --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Check out the latest news, polls and tools in the MSN 2004 Election Guide! http://special.msn.com/msn/election2004.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query an oracle table from a mysql database
Try PHP, it has built in functions to access both DBs HTH Steve Victor Pendleton wrote: You may want to look into using a programming language such as C or Java that communicates between both databases. -Original Message- From: Alonso, Claudio Fabian To: '[EMAIL PROTECTED]' Sent: 7/7/04 8:14 AM Subject: How to query an oracle table from a mysql database Importance: High Good morning, I need a MySQL database to be able to query a table in an Oracle database of a remote host. Is this possible? Could anybody tell me how to do it or where to find this information? Thanks in advance, --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to query an oracle table from a mysql database
You may want to look into using a programming language such as C or Java that communicates between both databases. -Original Message- From: Alonso, Claudio Fabian To: '[EMAIL PROTECTED]' Sent: 7/7/04 8:14 AM Subject: How to query an oracle table from a mysql database Importance: High Good morning, I need a MySQL database to be able to query a table in an Oracle database of a remote host. Is this possible? Could anybody tell me how to do it or where to find this information? Thanks in advance, --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query an oracle table from a mysql database
Good morning, I need a MySQL database to be able to query a table in an Oracle database of a remote host. Is this possible? Could anybody tell me how to do it or where to find this information? Thanks in advance, --Claudio ..
Re: How to query
I'm stumped... Now, I need to generate reports for a specific client. In the end, for a given month, I have to tell the client 1. Which invoices were for the purchase of blue widgets, only. 2. Which invoices were for the purchase of blue widgets and yellow widgets, together. 3. Which invoices for the purchase of green doodads, only. 4. Which for green doodads along with yellow widgets, together. etc. Out of 1,000 product codes, I'm interested in 10 or so. It's easy enough to write any one of the queries but I can't figure out how to group/order in such a way as to do it all in one query and to order by the 10 or so products I'm interested in. MySQL is 4.0.12. I guess I should be a little more specific. This is actually invoicing for services rendered. There are 1000+ codes for the various services, plus one additional, PREM, for premium processing. What the client needs to know is how many L1 visas were filed, how many with premium processing, how many L1 extensions, how many with premium processing, etc. The very nice and very smart lady who is running this report is using Crystal Reports and neither one of us knows what it's doing under the hood. But, in response to the trouble she is having, she wants to de-normalize the bejeebers out of MY tables by adding 10, or so, columns to the invoice table for each of the codes she's interested in. As you might guess, I'm dragging my heels, kicking and screaming, but that leaves it up to me to solve the problem. Any help would be appreciated. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query
I'm stumped... I have a pretty standard invoice system: create table invoice( InvoiceNum varhcar(16), ClientInfo ) create table line_items( InvoiceNum varchar(16), ProductCode varchar(10), Description varchar(25), Quantity . ) Three is, of course, a one-to-many relation between invoice and line_items. Now, I need to generate reports for a specific client. In the end, for a given month, I have to tell the client 1. Which invoices were for the purchase of blue widgets, only. 2. Which invoices were for the purchase of blue widgets and yellow widgets, together. 3. Which invoices for the purchase of green doodads, only. 4. Which for green doodads along with yellow widgets, together. etc. Out of 1,000 product codes, I'm interested in 10 or so. It's easy enough to write any one of the queries but I can't figure out how to group/order in such a way as to do it all in one query and to order by the 10 or so products I'm interested in. MySQL is 4.0.12. Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query mySQL in java without using actual column names? but using something like field(0),field(1) etc.,
At 10:38 -0500 11/8/03, karthikeyan wrote:
I have a table with about 100 column with different column name. It is
difficult to remember all the field
names. But I know the last few columns are very important for query and
used frequently.
You cannot perform comparisons on columns by using column positions.
If they're that important, why is it any more difficult to remember
their names than to remember their positions?
If you really want to use positions, you'll have to do something like
issuing a SHOW COLUMNS statement, read the result to see which columns
are at which positions. That will give you the appropriate column
names. Seems like a lot of work just to avoid knowing the column
names. :-)
For example:
Table with fields weight_42(double),width_43(double),height_44(double)
where the fields(42), field(43) and field(44) respectively
mysql> select * from data_base where weight_42 < 45.23 or width_43 <
43.23 or height_44 < 43.23 ;
can i query using :
mysql> select * from data_base where field(43) < 45.23 and field(44) <
45.23 and field(45) < 45.23;
or to use in java:
float j=43.25;
for(i=43;i<=45;i++)
{
S1=select * from data_base where field(" + i +") < " + j;
QUERY,
RESULT SET
etc.,
}
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query mySQL in java without using actual column names? but using something like field(0),field(1) etc.,
I have a table with about 100 column with different column name. It is
difficult to remember all the field
names. But I know the last few columns are very important for query and
used frequently.
For example:
Table with fields weight_42(double),width_43(double),height_44(double)
where the fields(42), field(43) and field(44) respectively
mysql> select * from data_base where weight_42 < 45.23 or width_43 <
43.23 or height_44 < 43.23 ;
can i query using :
mysql> select * from data_base where field(43) < 45.23 and field(44) <
45.23 and field(45) < 45.23;
or to use in java:
float j=43.25;
for(i=43;i<=45;i++)
{
S1=select * from data_base where field(" + i +") < " + j;
QUERY,
RESULT SET
etc.,
}
Thank you
M.Karthikeyan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query for 25 records before and 25 records after a record alphabetically
ORDER BY and LIMIT. Jeremy March wrote: I have an alphabetic list of records in a table and I want to display the 25 previous and 25 next records before and after a selected record. This would be easy if "before" and "after" meant sorted by id number, but I need it sorted alphabetically. Does anyone know a good way to do this? Thanks, Jeremy March _ Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query for 25 records before and 25 records after a record alphabetically
I have an alphabetic list of records in a table and I want to display the 25 previous and 25 next records before and after a selected record. This would be easy if "before" and "after" meant sorted by id number, but I need it sorted alphabetically. Does anyone know a good way to do this? Thanks, Jeremy March _ Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to query an entire row?
At 13:53 24/02/03 +0100, you wrote: Hello. On Mon 2003-02-24 at 11:28:05 +, [EMAIL PROTECTED] wrote: > Hello, > > In a table like this: > > ID > Item1 char(100) > Item2 char(100) > . > . > ItemN char(100) > > What's the cleanest way to do this mysql query: > > SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' > > Only way I can think to do it is: > > SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE > '%mysearch%' OR ) Yes, that's it. If you think that's unpretty, you are right. With a normalized design, you usually shouldn't need such a query. In other words, if you find yourself needing to do such queries regularly, you may want to re-evaluate your database design. Can I not do: WHERE CONCAT(Item1,Item2,ItemN) LIKE '%mysearch%' ? Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to query an entire row?
Hello. On Mon 2003-02-24 at 11:28:05 +, [EMAIL PROTECTED] wrote: > Hello, > > In a table like this: > > ID > Item1 char(100) > Item2 char(100) > . > . > ItemN char(100) > > What's the cleanest way to do this mysql query: > > SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' > > Only way I can think to do it is: > > SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE > '%mysearch%' OR ) Yes, that's it. If you think that's unpretty, you are right. With a normalized design, you usually shouldn't need such a query. In other words, if you find yourself needing to do such queries regularly, you may want to re-evaluate your database design. Depending on the context, a look at FULLTEXT indexes may be helpful, too. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to query an entire row?
Hello, In a table like this: ID Item1 char(100) Item2 char(100) . . ItemN char(100) What's the cleanest way to do this mysql query: SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' Only way I can think to do it is: SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE '%mysearch%' OR ) Many thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --- How to query results of a query?
On Wed, Jan 15, 2003 at 11:55:44PM +, [EMAIL PROTECTED] wrote: > > please look at page 194 of the reference manual... > > >(and if so how do you ask MySQL to create a temp table from the > > results of aquery?) > > here's an example: > > mysql> create temporary table tmp (name varchar(20), owner varchar(20, species >varchar(10)); > > mysql> insert into tmp select name, owner, species from pet where species='Dog'; Or in 1 go: create temporary table tmp select name, owner, species from pet where species='Dog'; Of it is small: create temporary table tmp type=heap select name, owner, species from pet where species='Dog'; for memory based one. Harmen (Sql, select, etc) -- The Moon is Waxing Gibbous (95% of Full) tty.nl - 2dehands.nl: 59340 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:--- How to query results of a query?
please look at page 194 of the reference manual... >(and if so how do you ask MySQL to create a temp table from the > results of aquery?) here's an example: mysql> create temporary table tmp (name varchar(20), owner varchar(20, species varchar(10)); mysql> insert into tmp select name, owner, species from pet where species='Dog'; These 2 statements put the results of the "select name, owner, species from pet where..." query in the temporary table tmp. thanks Nasser. == How do you query the table that is the results of a query? Must you ask MySQL to... create a temporary table form the results of the first query then query that temporary table then delete the temp table when you are done? (and if so how do you ask MySQL to create a temp table from the results of a query?) Is there a better and faster way to do this with minimum burden on the web server with the db on it? Thanks for any help. Will - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --- How to query results of a query?
Hello. Please start a new thread instead of replying to an existing one. Or else, your message will be sorted with the original thread for people with decent mail readers. On Wed 2003-01-15 at 14:42:05 -0500, [EMAIL PROTECTED] wrote: > How do you query the table that is the results of a query? By a sub-query. Since MySQL supports sub-queries only since version 4.1 (alpha), you have to work around this limitiation. The general answer can be found in the manual: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html > Must you ask MySQL to... > ...create a temporary table form the results of the first query > ...then query that temporary table > ...then delete the temp table when you are done? That is one possible solution (also mentioned in the manual page I cited). The third step optional if you use the TEMPORARY keyword with the table, because it will be deleted automatically when the conncetion is closed. > (and if so how do you ask MySQL to create a temp table from the > results of a query?) See http://www.mysql.com/doc/en/example-Maximum-column-group-row.html, which shows a work-around to a query which typically needs a sub-select. > Is there a better and faster way to do this with minimum burden on > the web server with the db on it? No. Btw, in the cases where you cannot rewrite a sub-select into a join, most often an RDBMS will so the equivalent of a temporary table. So there is not much loss, except for the additional transfer and parsing of the queries. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --- How to query results of a query?
If you're in Java you can call getMetaData on the ResultSet, and then call methods on that (ResultSetMetaData) to determine number of columns, and type of each column. This avoids the steps of creating/deleting a temp table. What language are you using? > > From: Will Standley <[EMAIL PROTECTED]> > Subject: --- How to query results of a query? > Date: 16/01/2003 6:42:05 > To: [EMAIL PROTECTED] > > How do you query the table that is the results of a query? > > Must you ask MySQL to... > ...create a temporary table form the results of the first query > ...then query that temporary table > ...then delete the temp table when you are done? > > (and if so how do you ask MySQL to create a temp table from the results of a > query?) > > Is there a better and faster way to do this with minimum burden on the web > server with the db on it? > > Thanks for any help. > > Will > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > This message was sent through MyMail http://www.mymail.com.au - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--- How to query results of a query?
How do you query the table that is the results of a query? Must you ask MySQL to... ...create a temporary table form the results of the first query ...then query that temporary table ...then delete the temp table when you are done? (and if so how do you ask MySQL to create a temp table from the results of a query?) Is there a better and faster way to do this with minimum burden on the web server with the db on it? Thanks for any help. Will - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to query a varchar column's value staring with a string?
Hi there, I found a way to do it with the following query select * from theTable where 'test' like concat( theColumn, '%' ); Thanks for the help, Andre --- Andre Kirchner <[EMAIL PROTECTED]> wrote: > thanks for the help, but what I really want is to > select all the values that are a substring of 'test' > as 't', 'te', 'tes' and 'test', and so I was trying > the query > > select * from theTable > where ( theColumn || '%' ) like 'test'; > > I think my previous email as a litle bit confusing. > Sorry for that :-) > > Andre > > --- Jennifer Goodie <[EMAIL PROTECTED]> > wrote: > > select * from theTable where theColumn like > 'test%'; > > or > > select * from theTable where LEFT(theColumn,4) = > > 'test'; > > > > depending on the table structure and data and > > indexing, etc. I have seen a > > performance difference between the two, test and > see > > which works best for > > your application. > > > > > > Hi there, > > > > how can I query all the values starting with > 'test'? > > I tried the following query, but it didn't work > > > > select * from theTable where ( ( theColumn || '%' > ) > > like 'test' ); > > > > Thanks, > > > > Andre > > mySQL > > > > __ > > Do you Yahoo!? > > HotJobs - Search new jobs daily now > > http://hotjobs.yahoo.com/ > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list > > archive) > > > > To request this thread, e-mail > > <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > > http://lists.mysql.com/php/unsubscribe.php > > > > > __ > Do you Yahoo!? > HotJobs - Search new jobs daily now > http://hotjobs.yahoo.com/ > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to query a varchar column's value staring with a string?
thanks for the help, but what I really want is to select all the values that are a substring of 'test' as 't', 'te', 'tes' and 'test', and so I was trying the query select * from theTable where ( theColumn || '%' ) like 'test'; I think my previous email as a litle bit confusing. Sorry for that :-) Andre --- Jennifer Goodie <[EMAIL PROTECTED]> wrote: > select * from theTable where theColumn like 'test%'; > or > select * from theTable where LEFT(theColumn,4) = > 'test'; > > depending on the table structure and data and > indexing, etc. I have seen a > performance difference between the two, test and see > which works best for > your application. > > > Hi there, > > how can I query all the values starting with 'test'? > I tried the following query, but it didn't work > > select * from theTable where ( ( theColumn || '%' ) > like 'test' ); > > Thanks, > > Andre > mySQL > > __ > Do you Yahoo!? > HotJobs - Search new jobs daily now > http://hotjobs.yahoo.com/ > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to query a varchar column's value staring with a string?
select * from theTable where theColumn like 'test%'; or select * from theTable where LEFT(theColumn,4) = 'test'; depending on the table structure and data and indexing, etc. I have seen a performance difference between the two, test and see which works best for your application. Hi there, how can I query all the values starting with 'test'? I tried the following query, but it didn't work select * from theTable where ( ( theColumn || '%' ) like 'test' ); Thanks, Andre mySQL __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to query a varchar column's value staring with a string?
Hi there, how can I query all the values starting with 'test'? I tried the following query, but it didn't work select * from theTable where ( ( theColumn || '%' ) like 'test' ); Thanks, Andre mySQL __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to query for the primary key?
justin, Friday, June 14, 2002, 4:52:12 AM, you wrote: jc> This seems like a dumb question--sorry. Looking at my table it shows jc> 'MUL' instead of 'PRI' like the other tables. Did I forget to code jc> unit_id as primary? Thanks, Justin jc> mysql> desc property_units; jc> ++-+--+-+-++ jc> | Field | Type| Null | Key | Default | Extra | jc> ++-+--+-+-++ jc> | unit_id| int(11) | | MUL | 0 | auto_increment | jc> | property_id| int(11) | YES | | NULL|| jc> | unit_detail_id | int(11) | YES | | NULL|| jc> | unit_type_id | int(11) | YES | | NULL|| jc> | date_available | varchar(20) | YES | | NULL|| jc> ++-+--+-+-++ Please, show me the output of following: SHOW CREATE TABLE property_units; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to query for the primary key?
Hi, > This seems like a dumb question--sorry. Looking at my table it shows > 'MUL' instead of 'PRI' like the other tables. Did I forget to code > unit_id as primary? Thanks, Justin mysql> show index from property_units; Regards, Sasa »mysql, select, database« - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to query for the primary key?
This seems like a dumb question--sorry. Looking at my table it shows 'MUL' instead of 'PRI' like the other tables. Did I forget to code unit_id as primary? Thanks, Justin mysql> desc property_units; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | unit_id| int(11) | | MUL | 0 | auto_increment | | property_id| int(11) | YES | | NULL|| | unit_detail_id | int(11) | YES | | NULL|| | unit_type_id | int(11) | YES | | NULL|| | date_available | varchar(20) | YES | | NULL|| ++-+--+-+-++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to query SET OPTION variables?
On Fri, May 03, 2002 at 01:22:30PM +0200, Harald Fuchs wrote: > > How about "SHOW OPTIONS [LIKE wild]", nicely accompanying "SHOW VARIABLES"? Yeah, that'd be the way to go, I think. -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 85 days, processed 2,227,635,975 queries (300/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to query SET OPTION variables?
Hi. On Thu, May 02, 2002 at 12:02:44AM -0700, [EMAIL PROTECTED] wrote: > On Tue, Apr 30, 2002 at 04:17:00PM +0200, Benjamin Pflugmann wrote: > > Hi. > > > > Maybe I am blind, but I cannot find, how I can query the status of > > one of the variables I can set with SET OPTION. I.e. if I do > > > > SET OPTION SQL_AUTO_IS_NULL 1 > > SET OPTION AUTO_COMMIT 1 > > > > What is / is there a way to query to current value? > > You're not blind. :-) Ah. Lucky me :-) Hm. Well, one of my tries was like SET OPTION SQL_AUTO_IS_NULL (without value, as with some shells) or, something like SET OPTION ?SQL_AUTO_IS_NULL (idea borrowed from mutt) SET OPTION SQL_AUTO_IS_NULL? SET OPTION SQL_AUTO_IS_NULL=? I avoid intentionally to use GET, because that may mean one more reserved word. Other/better suggestions? And I should get some MySQL developer to agree with the need. ;-) Well, btw, SET OPTION CHARACTER SET character_set_name | DEFAULT is the only one not using '='. Is there any reason why '=' is not used? SET OPTION CHARACTER SET = character_set_name | DEFAULT > > I stumbed upon this, when I wanted to recommend to someone to query > > the value of AUTO_COMMIT to assure that the client interface (Perl's > > DBD::ADO and DBD::mysql) has set the expected value. > > You mean compare the database handle's ($dbh) attribute with what the > server thinks is has set? Hm. Well, I guess I think so (Perl is not my strong side ;-). The point was, that the described behaviour was inconsistent compared to what should have been set by the different clients and I simply wanted to exclude some points of failure by knowing what the server thinks. So... yes :-) Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to query SET OPTION variables?
Hi. Maybe I am blind, but I cannot find, how I can query the status of one of the variables I can set with SET OPTION. I.e. if I do SET OPTION SQL_AUTO_IS_NULL 1 SET OPTION AUTO_COMMIT 1 What is / is there a way to query to current value? I stumbed upon this, when I wanted to recommend to someone to query the value of AUTO_COMMIT to assure that the client interface (Perl's DBD::ADO and DBD::mysql) has set the expected value. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to query???
Can anyone help me with this query??? I have a very big table (70.000 rows) which has several rows like this: idproductdescription 1P1desc1 2P1desc2 and I want to transform it into: idproductdescription 1P1desc1 desc2 I now how to do it in PHP but, is there anyway to do it with a SQL query???
How to query???
Can anyone help me with this query??? I have a very big table (70.000 rows) which has several rows like this: idproductdescription 1P1desc1 2P1desc2 and I want to transform it into: idproductdescription 1P1desc1 desc2 I now how to do it in PHP but, is there anyway to do it with a SQL query???
How to query???
Can anyone help me with this query??? I have a very big table (70.000 rows) which has several rows like this: idproductdescription 1P1desc1 2P1desc2 and I want to transform it into: idproductdescription 1P1desc1 desc2 I now how to do it in PHP but, is there anyway to do it with a SQL query???
RE: How to query and return nearest value...
Try something like this SELECT price, min(abs(price-value)) as Mini from table group by price order by Mini LIMIT 1 Patrick -Message d'origine- De : Robert Badaracco [mailto:[EMAIL PROTECTED]] Envoyé : mercredi 24 janvier 2001 05:47 À : [EMAIL PROTECTED] Objet : How to query and return nearest value... Hi, I have a range of decimal numbers (Prices) in a table column that I'd like to run a query against. I'd like to run a query with a value that returns the closest price to that value if it can't find a match. Is there some function that I can use in my query that will allow me to do this? Thanks, Bob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to query and return nearest value...
Hi, I have a range of decimal numbers (Prices) in a table column that I'd like to run a query against. I'd like to run a query with a value that returns the closest price to that value if it can't find a match. Is there some function that I can use in my query that will allow me to do this? Thanks, Bob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
