Problem with query
I am running this query: SELECT group_post_mod_option.option_id, group_post_mod_option.option_name, COUNT(group_post_moderation.group_post_moderation_option) AS count FROM group_post_mod_option LEFT OUTER JOIN group_post_moderation ON (group_post_mod_option.option_id = group_post_moderation.group_post_moderation_option AND group_post_moderation.group_post_id = 37) GROUP BY group_post_mod_option.option_id ORDER BY COUNT(group_post_moderation.group_post_moderation_option), group_post_mod_option.option_name; And getting this error: 'Invalid use of group function' Without the ORDER BY clause, the query works just fine. Anyone know what's going on? Regards, - Naz. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query
Naz, That query logic runs without a error on the server I have to hand (5.0.37), but it has three issues: (i) unless there is an exceptionless 1:1 relationship between group_post_mod_option.option_id and group_post_mod_option.option_name, results for the latter column will be meaningless because it is not aggregated. Absent a 1:1 relationship, you need a subquery to fetch the name. (ii) the WHERE condition group_post_moderation.group_post_id = 37 will remove all NULL rows from the right side of the join, turning the OUTER JOIN effectively into an INNER JOIN, so you might as well write it as an INNER JOIN (iii) why not use the alias `count` in the ORDER BY clause (the query engine would likely spot that)? So that would give something like... SELECT o.option_id, (SELECT option_name FROM group_post_mod_option.option_name WHERE option_id = o.option_id) AS name, COUNT(m.group_post_moderation_option) AS count FROM group_post_mod_option o JOIN group_post_moderation m ON o.option_id = m.group_post_moderation_option AND m.group_post_id = 37 GROUP BY o.option_id ORDER BY count, o.option_name; PB - Naz Gassiep wrote: I am running this query: SELECT group_post_mod_option.option_id, group_post_mod_option.option_name, COUNT(group_post_moderation.group_post_moderation_option) AS count FROM group_post_mod_option LEFT OUTER JOIN group_post_moderation ON (group_post_mod_option.option_id = group_post_moderation.group_post_moderation_option AND group_post_moderation.group_post_id = 37) GROUP BY group_post_mod_option.option_id ORDER BY COUNT(group_post_moderation.group_post_moderation_option), group_post_mod_option.option_name; And getting this error: 'Invalid use of group function' Without the ORDER BY clause, the query works just fine. Anyone know what's going on? Regards, - Naz. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with query on 5.11
Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? I'm using MySQL 5.1.11. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
Is 9640 a word by itself? A full-text search wouldn't find abc9640, you'd need to use LIKE for that. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Don O'Neil [mailto:[EMAIL PROTECTED] Sent: Friday, October 20, 2006 2:44 AM To: mysql@lists.mysql.com Subject: Problem with query on 5.11 Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? I'm using MySQL 5.1.11. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Jerry, Is 9640 a word by itself? A full-text search wouldn't find abc9640, No a full text search would find numbers pretending to be a word, the full text search has a fairly high level definition of a word. Try searching for 1960 over at http://ftvdb.bfi.org.uk/search.php. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Jerry, Is 9640 a word by itself? A full-text search wouldn't find abc9640, No a full text search would find numbers pretending to be a word, the full text search has a fairly high level definition of a word. Try searching for 1960 over at http://ftvdb.bfi.org.uk/search.php. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
Yes, there is a full text index, there are about 12,000 rows or so. Don -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, October 19, 2006 11:47 PM To: Don O'Neil Cc: mysql@lists.mysql.com Subject: Re: Problem with query on 5.11 Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
I just deleted and re-created my indexes and the query works now... I guess the index got corrupted somehow. Strange that I never saw any indications of this in messages, just that 0 rows were returned. Don -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, October 19, 2006 11:47 PM To: Don O'Neil Cc: mysql@lists.mysql.com Subject: Re: Problem with query on 5.11 Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Problem on query kind of like a group by
Everyone, I finally feel let down by mysql after 5 years of great use. I break most things in weeks so this is a heck of a record. I am sure I am being a dummy on this, but am wondering if there is some setting somewhere to help out a query like this. Given a table like this: FeatureID is an autonumber and the group field keeps things together aka version control. FeatureID, FeatureGroupID, FeatureName 1, 1, Version 1 2, 1, Version 2 3, 1, Version 3 4, 1, Version 4 ... 999, 1, Version 999 Over small sets I write a query like this and get stuff quickly: Select FeatureName from Feature F where FeatureID = (Select max(FeatureID) from Feature where FeatureGroupID = F.FeatureGroupID) Giving me Verison 999 In MySQL for 1000 versioned records this takes almost half a second on my machine. On my machine for MS SQL this returns right away basically in no time. I make sure cache is not in play by inserting a set of records and then running the query. Also the FeatureGroupID is indexed and so is the featureID. Is there a setting to make this work in mysql administration somewhere? Is there a word or set of words that I can search for? I have looked high and low and have not been able to make this work fast enough for me today. Someone I work with suggested using group by and this seems to be even worse then what I am doing in the above query like seconds or longer. h Also note that also can be considered records in the table such that 1000,2, Version 2 1 1001,2, Version 2 2 1002,2, Version 2 2 The query Select FeatureName from Feature where FeatureID = (Select max(FeatureID) from Feature) will not work for me since i want all of the highest FeatureID'd, FeatureGroupID'd stuff. I have some settings in my my.cnf like: join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M read_rnd_buffer_size = 3m These have not had any effect. Also caching is not an option because the first hit in one of my more complicated use cases takes over 7 seconds. Someone please help. karma is yours if you can help me on this. Thank You Jim Tyrrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Problem on query kind of like a group by
Jim Tyrrell wrote:
Everyone,
I finally feel let down by mysql after 5 years of
great use. I break most things in weeks so this is a
heck of a record. I am sure I am being a dummy on
this, but
am wondering if there is some setting somewhere to
help out a query like this.
Given a table like this:
FeatureID is an autonumber and the group field keeps
things together aka version control.
FeatureID, FeatureGroupID, FeatureName
1, 1, Version 1
2, 1, Version 2
3, 1, Version 3
4, 1, Version 4
...
999, 1, Version 999
Over small sets I write a query like this and get
stuff quickly:
Select FeatureName from Feature F where FeatureID =
(Select max(FeatureID) from Feature where
FeatureGroupID = F.FeatureGroupID)
Giving me Verison 999
In MySQL for 1000 versioned records this takes almost
half a second on my machine. On my machine for MS SQL
this returns right away basically in no time. I make
sure cache is not in play by inserting a set of
records and then running the query. Also the
FeatureGroupID is indexed and so is the featureID.
Is there a setting to make this work in mysql
administration somewhere?
Is there a word or set of words that I can search for?
I have looked high and low and have not been able to
make this work fast enough for me today.
Someone I work with suggested using group by and this
seems to be even worse then what I am doing in the
above query like seconds or longer. h
Also note that also can be considered records in the
table such that
1000,2, Version 2 1
1001,2, Version 2 2
1002,2, Version 2 2
The query Select FeatureName from Feature where
FeatureID = (Select max(FeatureID) from Feature)
will not work for me since i want all of the highest
FeatureID'd, FeatureGroupID'd stuff.
I have some settings in my my.cnf like:
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
read_rnd_buffer_size = 3m
These have not had any effect.
Also caching is not an option because the first hit in
one of my more complicated use cases takes over 7
seconds.
Someone please help.
karma is yours if you can help me on this.
Thank You
Jim Tyrrell
Speed questions usually come down to having the right indexes on the tables
involved, and writing queries so the indexes are properly used. The settings in
my.cnf are rarely the issue.
First, you need to find the max FeatureID for each FeatureGroupID. This will go
fastest with a multi-column index on (FeatureGroupID, FeatureID), in that order.
Do you have that? {We'd know if you had included the output of SHOW CREATE
TABLE Feature.} A multi-column index on (FeatureGroupID, FeatureID) will also
function as a single-column index on (FeatureGroupID), so you don't need a
separate index on that column.
Next, you want to find the rows in table Feature whose FeatureGroupID and
FeatureID match the results of step one. This is a frequently asked question,
with 3 solutions given in the manual
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html.
The next question is whether or not mysql is using the index to optimize your
query. Your query is the subquery solution from the above-referenced manual
page. Unfortunately, mysql doesn't always optimize queries with subqueries
properly. To find out what mysql is doing, put EXPLAIN in front of your query
http://dev.mysql.com/doc/refman/4.1/en/explain.html. Post the results if you
need help interpreting them.
If it turns out that the subquery has fooled mysql into not using the index to
help your query, then the temporary table solution in the manual will almost
certainly be faster:
# Replace INT as needed to match the types of these fields in table Feature
CREATE TEMPORARY TABLE max_fids (FeatureGroupID INT, FeatureID INT);
# Avoid anyone changing the data in the middle
LOCK TABLES Feature READ;
# Step 1: Get the max FeatureID for each FeatureGroupID
INSERT INTO max_fids SELECT FeatureGroupID, MAX(FeatureID)
FROM Feature GROUP BY FeatureGroupID;
#Step 2: Find the rows in Feature which match the results in Step 1 via a JOIN
SELECT f.FeatureName,
FROM max_fids m
JOIN Feature f
ON f.FeatureGroupID = m.FeatureGroupID
AND f.FeatureID = m.FeatureID;
#clean up
UNLOCK TABLES;
DROP TABLE max_fids;
That looks a lot more complex, but as the index on (FeatureGroupID, FeatureID)
will almost certainly be used for both steps, it should be very fast.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with query
Quite new to MySQl and queries in general and would like some help in the following query: select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, prCompletionDate, prActive from tProject where prDate =2005-05-09 It generates a listing that has years from 2001 to present. All I'm looking for is information start from 2005-05-09 to present. Thanks !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=us-ascii META content=MSHTML 6.00.2900.2627 name=GENERATOR/HEAD BODY style=MARGIN-TOP: 2px; FONT: 10pt Arial; MARGIN-LEFT: 2px DIVQuite new to MySQl and queries in general and would like some help in the following query:/DIV DIVselect prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, prCompletionDate, prActive from tProject where prDate gt;=2005-05-09/DIV DIVnbsp;/DIV DIVIt generates a listing that has years from 2001 to present.nbsp; All I'm looking for is information start from 2005-05-09 to present./DIV DIVnbsp;/DIV DIVThanksBR/DIV/BODY/HTML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query
From: Jon Miller [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Problem with query Date: Tue, 24 May 2005 19:40:32 +0800 Quite new to MySQl and queries in general and would like some help in the following query: select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, prCompletionDate, prActive from tProject where prDate =2005-05-09 It generates a listing that has years from 2001 to present. All I'm looking for is information start from 2005-05-09 to present. Thanks TEXT.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Try putting quotes around the date select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, prCompletionDate, prActive from tProject where prDate = '2005-05-09' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query and password
Hello.
Looks like passwords in your table are stored in old short format.
Use old_password() instead of password(). See:
http://dev.mysql.com/doc/mysql/en/password-hashing.html
Lancer Emotion 16 [EMAIL PROTECTED] wrote:
Hello everbody,i have a problem with mysql and i wish you could help me.
I have this table named users :
+++--+--+---+-+-
+
| ID | user | pass | thegroup | firstname | surname |
enabled
|
+++--+--+---+-+-
+
| 1 | Admin | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
|
| 2 | admin2 | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
|
+++--+--+---+-+-
The password in both cases are : admin .
When i do any of this querys :
select * from users where pass=password(admin);
select * from users where pass='admin';
select * from users where pass=password('admin');
i get an empty set, i dont know why.
When i do :
select password('admin')
i get : *4ACFE3202A5FF5CF467898FC58AAB1D615029441
Please help me.
Thanks
--
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]
Problem with query and password
Hello everbody,i have a problem with mysql and i wish you could help me.
I have this table named users :
+++--+--+---+-+-
+
| ID | user | pass | thegroup | firstname | surname | enabled
|
+++--+--+---+-+-
+
| 1 | Admin | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
|
| 2 | admin2 | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
|
+++--+--+---+-+-
The password in both cases are : admin .
When i do any of this querys :
select * from users where pass=password(admin);
select * from users where pass='admin';
select * from users where pass=password('admin');
i get an empty set, i dont know why.
When i do :
select password('admin')
i get : *4ACFE3202A5FF5CF467898FC58AAB1D615029441
Please help me.
Thanks
--
lancer emotion 16
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query and password
Lancer Emotion 16 wrote:
I have this table named users :
+
| ID | user | pass | thegroup | firstname | surname | enabled
+++--+--+---+-+-
| 1 | Admin | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
The password in both cases are : admin .
When i do any of this querys :
select * from users where pass=password(admin);
that one should work, except...
When i do :
select password('admin')
i get : *4ACFE3202A5FF5CF467898FC58AAB1D615029441
You wouldn't maybe guess that your pass field above is too small? :-)
FWIW,
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and VBquestion - problem with query that returns 100000+ records
Shawn I took your advice and let the app run for a good 12+ minutes - miraculously it worked and the resulting report did show up.I am now convinced that the status shown in the task manager is misleading. I am trying to implement your suggestion of using an asynchronous call. So I am doing something like Set Rs = New ADODB.Recordset Rs.CursorLocation = adUseServer 'adUseClient Rs.Open SQL, connection, adOpenForwardOnly, adLockReadOnly, adAsyncFetch Question is how do i poll for the status of adStateExecuting and adStateFetching ? Will the execution get to the 'do loop' at all (before the Rs.Open has completed) if I do something like: Rs.Open SQL, connection, adOpenForwardOnly, adLockReadOnly, adAsyncFetch do while (adStateExecuting and adStateFetching ) . . DoEvents loop OR Do I need to do something like Dim WithEvents Rs as ADODB.recordset and then use the corresponding event handler. The irony of the entire situation is that we were thinking of migrating to MySQL in hopes of improving the performance over MS Access. While there might me other advantages of using MySQL over MS Access, it is definitely failing on the performance front and thats a shame. I am also going to try your modified query and will post the results of the EXPLAIN soon. Thanks a lot for all your help; Greg From: [EMAIL PROTECTED] To: Greg Zimmermack [EMAIL PROTECTED] Subject: Re: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 12:37:16 -0400 Greg, I think what you are calling hung may really be a blocked condition. Using ADO, you have the option of your queries being synchronous or asynchronous. A synchronous query will wait for the results before returning control to your program. In other words, your application hands over total control of it's execution to ADO. ADO submits your query then goes to sleep until one of several things occurs: it gets a response from the server, it times out, or any of the other events that will also rouse ADO from its slumber. The task manager in windows checks the status of the various executing applications by sending them windows messages. Since your application is in essence frozen until ADO returns from its call, your application will not respond to any windows messages. Other things like move window, resize window, and close window are also windows messages so it will seem to be completely unresponsive to any action you take until ADO releases its control over the situation. Try setting up the request as an asynchronous call (set the option on your open to adAsynchFetch) and periodically monitoring the state of your recordset for adStateExecuting and adStateFetching (may I suggest a do...loop, a while...wend, or a timer object? ) to see if you are bound by the query's execution or the data retrieval. Your application will also be able to respond to the windows messages as its default message handler will not be waiting on ADO to return. (You can force the default message handler to deal with any outstanding messages by executing a DoEvents call.) You said earlier that if you tried the query manually, you only saw 1000 rows. That clues me in that you are using one of the GUI admin tools as your manual interface. Some of them limit database responses to only the first 1000 rows. I don't know if you can change that value or not, you will have to consult the docs for your tool. However, in the same location where you execute your SQL by hand just add the word EXPLAIN to the beginning of it - EXPLAIN SELECT . (rest of statement) - and you will get a response from the server detailing how the engine plans to deal with that particular query. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Greg Zimmermack [EMAIL PROTECTED]To: [EMAIL PROTECTED] ail.com cc: Fax to: 06/30/2004 12:00 Subject: Re: MySQL and VBquestion - problem with query that returns PM10+ records Thanks for your reply Shawn I will try running the modified query you sent me and check if that helps - meanwhile here are the replies to some of your questions. *I know the application is hung by checking its status in the Windows Task manager( it shows the status as 'Not Responding'). It looks like the app hangs before the query is done executing *The version of MySQL is 4.0.16-nt *I am not sure how to do the EXPLAIN you mentioned. *I removed the ORDERBY clause but that did not make any difference. Appreciate your help Greg From: [EMAIL PROTECTED] To: Greg Zimmermack [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 11:01:29 -0400 Your SQL syntax
MySQL and VBquestion - problem with query that returns 100000+ records
I have an SQL which looks something like: SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName FROM (((Provider PR INNER JOIN (Person P INNER JOIN Transaction T ON P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy = S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0)) ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC This query is invoked from a Visual Basic 6.0 application that uses ADO ODBC to connect to a MySQL database. The query runs fine on a small test database but when i run it on the production database the application just hangs up without generating any error messages. The query is expected to return some 196000 records from the production database. Can this (large number of records) be causing the problem? Is there any setting in MySQL that I could change ? If i run the query on the server i can see upto 1000 records but no more. This is my first attempt at MySQL hence the newbie type questions. I should also mention that the application in question originally used an MS Access database and it can pull all the 196000 records from it. Any help will be greatly appreciated. Thanks Greg _ Get fast, reliable Internet access with MSN 9 Dial-up now 3 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and VBquestion - problem with query that returns 100000 + records
While the Visual Basic application is querying the database can you log into the MySQL via the mysql monitor and do a show processlist to see if the query is executing? If not I would suggest you view the mysql error log or set up Visual basic to throw and error when it is getting disconnected. -Original Message- From: Greg Zimmermack To: [EMAIL PROTECTED] Sent: 6/30/04 9:13 AM Subject: MySQL and VBquestion - problem with query that returns 10+ records I have an SQL which looks something like: SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName FROM (((Provider PR INNER JOIN (Person P INNER JOIN Transaction T ON P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy = S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0)) ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC This query is invoked from a Visual Basic 6.0 application that uses ADO ODBC to connect to a MySQL database. The query runs fine on a small test database but when i run it on the production database the application just hangs up without generating any error messages. The query is expected to return some 196000 records from the production database. Can this (large number of records) be causing the problem? Is there any setting in MySQL that I could change ? If i run the query on the server i can see upto 1000 records but no more. This is my first attempt at MySQL hence the newbie type questions. I should also mention that the application in question originally used an MS Access database and it can pull all the 196000 records from it. Any help will be greatly appreciated. Thanks Greg _ Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/ -- 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: MySQL and VBquestion - problem with query that returns 100000+ records
Your SQL syntax is fine, I just get a better view of what is joining to what if I format it a little differently (I also avoid a lot of line wrapping this way too): SELECT S.FirstName , T.CreateDateTime , T.TranDateTime , P.FirstName , P.LastName , D.DiagnosisIDString , T.CheckNumber , T.StmtDesc , T.ServPayAdjIDString , T.PatientAmt , T.InsuranceAmt , T.ClaimID , T.AuditDateTime , T.TransactionType , T.ProviderID , PR.FirstName , PR.LastName FROM Provider PR INNER JOIN Transaction T ON PR.ProviderID = T.ProviderID INNER JOIN Person P ON P.PersonID = T.PersonID INNER JOIN Staff S ON T.CreatedBy = S.StaffID LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID WHERE T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC It may not be hung but just taking a *long* time to resolve this query. Did the error log show anything? Do all of your tables have the appropriate indexes to speed up your JOIN statements? Could you post the results of an EXPLAIN on your query? What version of MySQL are you using? You might be able to speed this up by running it as two queries (using a temp table to store the results of the first part of the query) rather than one large query. Depending on how many columns you have in each table, your internal tableset (the result of all of those joins) could contain hundreds of columns. By splitting it into a couple of smaller steps you help keep the number of unused columns per stage to a reasonable number. You may also get more speed out of this if you move your WHERE constraints into the appropriate ON clauses: FROM Provider PR INNER JOIN Transaction T ON PR.ProviderID = T.ProviderID AND T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 INNER JOIN Person P ON P.PersonID = T.PersonID AND T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 INNER JOIN Staff S ON T.CreatedBy = S.StaffID AND T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID AND T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC It looks bulkier but if you do it this way you give the MySQL engine every possible opportunity to pare down your JOINed datasets. Generally, the less data you have to process, the faster everything will go. You *cannot* always move a WHERE condition into an ON clause, but in this case it was possible to do so with all of your WHERE conditions. This *is* a case-by-case optimization. And, last but not least, you may want to read up on an ongoing discussion of ways to reduce the speed hit when using ORDER BY...DESC. It is a known issue. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Greg Zimmermack [EMAIL PROTECTED]To: [EMAIL PROTECTED] ail.com cc: Fax to: 06/30/2004 10:13 Subject: MySQL and VBquestion - problem with query that returns AM10+ records I have an SQL which looks something like: SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName FROM (((Provider PR INNER JOIN (Person P INNER JOIN Transaction T ON P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy = S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0)) ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC This query is invoked from a Visual Basic 6.0 application that uses ADO ODBC to connect to a MySQL database
RE: MySQL and VBquestion - problem with query that returns 100000+ records
Victor Thanks for your reply. I checked the processlist and it continues to execute even after the VB application is hung. I know the application is hung by checking its status in the Windows Task manager. The 'hostname.err' file did not show any errors. Is there another file i should be looking at? The error handler in in VB could not handle the error. Is there some ODBC setting I need to optimize? Any help will be appreciated. Thanks Greg From: Victor Pendleton [EMAIL PROTECTED] To: 'Greg Zimmermack ' [EMAIL PROTECTED],'[EMAIL PROTECTED] ' [EMAIL PROTECTED] Subject: RE: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 09:19:11 -0500 While the Visual Basic application is querying the database can you log into the MySQL via the mysql monitor and do a show processlist to see if the query is executing? If not I would suggest you view the mysql error log or set up Visual basic to throw and error when it is getting disconnected. -Original Message- From: Greg Zimmermack To: [EMAIL PROTECTED] Sent: 6/30/04 9:13 AM Subject: MySQL and VBquestion - problem with query that returns 10+ records I have an SQL which looks something like: SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName FROM (((Provider PR INNER JOIN (Person P INNER JOIN Transaction T ON P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy = S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0)) ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC This query is invoked from a Visual Basic 6.0 application that uses ADO ODBC to connect to a MySQL database. The query runs fine on a small test database but when i run it on the production database the application just hangs up without generating any error messages. The query is expected to return some 196000 records from the production database. Can this (large number of records) be causing the problem? Is there any setting in MySQL that I could change ? If i run the query on the server i can see upto 1000 records but no more. This is my first attempt at MySQL hence the newbie type questions. I should also mention that the application in question originally used an MS Access database and it can pull all the 196000 records from it. Any help will be greatly appreciated. Thanks Greg _ Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/ -- 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] _ From will you? to I do, MSN Life Events is your resource for Getting Married. http://lifeevents.msn.com/category.aspx?cid=married -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and VBquestion - problem with query that returns 100000 + records
In ODBC applications, I have seen the application give a not responding message when the database is taking longer than expected to return a result set. ... You say you see the query still executing? So the select statement is just taking a long time to process? If this is so I would try to optimize the query. Have you run an explain plan on the query to see the execution path? ... You could also try using the ODBC trace feature in the 'ODBC Data Source Administrator' -Original Message- From: Greg Zimmermack To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 6/30/04 10:52 AM Subject: RE: MySQL and VBquestion - problem with query that returns 10+ records Victor Thanks for your reply. I checked the processlist and it continues to execute even after the VB application is hung. I know the application is hung by checking its status in the Windows Task manager. The 'hostname.err' file did not show any errors. Is there another file i should be looking at? The error handler in in VB could not handle the error. Is there some ODBC setting I need to optimize? Any help will be appreciated. Thanks Greg From: Victor Pendleton [EMAIL PROTECTED] To: 'Greg Zimmermack ' [EMAIL PROTECTED],'[EMAIL PROTECTED] ' [EMAIL PROTECTED] Subject: RE: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 09:19:11 -0500 While the Visual Basic application is querying the database can you log into the MySQL via the mysql monitor and do a show processlist to see if the query is executing? If not I would suggest you view the mysql error log or set up Visual basic to throw and error when it is getting disconnected. -Original Message- From: Greg Zimmermack To: [EMAIL PROTECTED] Sent: 6/30/04 9:13 AM Subject: MySQL and VBquestion - problem with query that returns 10+ records I have an SQL which looks something like: SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName FROM (((Provider PR INNER JOIN (Person P INNER JOIN Transaction T ON P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy = S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0)) ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC This query is invoked from a Visual Basic 6.0 application that uses ADO ODBC to connect to a MySQL database. The query runs fine on a small test database but when i run it on the production database the application just hangs up without generating any error messages. The query is expected to return some 196000 records from the production database. Can this (large number of records) be causing the problem? Is there any setting in MySQL that I could change ? If i run the query on the server i can see upto 1000 records but no more. This is my first attempt at MySQL hence the newbie type questions. I should also mention that the application in question originally used an MS Access database and it can pull all the 196000 records from it. Any help will be greatly appreciated. Thanks Greg _ Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/ -- 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] _ From 'will you?' to 'I do,' MSN Life Events is your resource for Getting Married. http://lifeevents.msn.com/category.aspx?cid=married -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and VBquestion - problem with query that returns 100000+ records
I have to ask, why would you even want to pull that many records at once? No user would want to sift through that many records. I always add a limit clause to all my queries. There is no harm in having a limit 500 when you are just receiving 10 records. But it's good to have the limit there in case your query has a bug. It may be that things are hanging on the transfer of that much data from MySQL to ODBC to VB. If you add a LIMIT 25 at the end of the query statement, does it work then? On Jun 30, 2004, at 11:52 AM, Greg Zimmermack wrote: Victor Thanks for your reply. I checked the processlist and it continues to execute even after the VB application is hung. I know the application is hung by checking its status in the Windows Task manager. The 'hostname.err' file did not show any errors. Is there another file i should be looking at? The error handler in in VB could not handle the error. Is there some ODBC setting I need to optimize? Any help will be appreciated. Thanks Greg -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with query
Dear Friends Running the follow query select max( sr_recno) from test_table down work for re the error id returned by mysql_errno is 3731792 server is 4.1.1a-alpha Table structure is SR_DELETEC char(1) SR_RECNO bigint(15) PRI auto_increment VALUE double(18,6) OBS text ENABLE tinyint(4) DATE_LIM date days double(6) percent double(8,2) descr varchar(50) cardid char(1) code_id varchar(8) Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with query
Luiz Rafael Culik Guimaraes wrote: Dear Friends Running the follow query select max( sr_recno) from test_table down work for re the error id returned by mysql_errno is 3731792 server is 4.1.1a-alpha Table structure is SR_DELETEC char(1) SR_RECNO bigint(15) PRI auto_increment VALUE double(18,6) OBS text ENABLE tinyint(4) DATE_LIM date days double(6) percent double(8,2) descr varchar(50) cardid char(1) code_id varchar(8) Does it work from command line client? If yes, double-check your client code to make sure it has no bugs. If not, it would be a bug in MySQL itself, and you need to prepare a test case for a bug report. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with query (group, count)
Hello to all, I searched this list's archive, but I couldn't find a solution to a problem I am currently facing. Any help would be appreciated. I have the next table configuration: session (an session id), weight (a counter), and other fields. When I add a new record and the session id I am tring to add doesn't exist in the table, the weight field is set to 1, other wise the weight field is set to the maximum weight field + 1 found for the same session id. The problem: I want a query to get all the distinct session ids, but for each session id I want it to return the last record in the order of the weight field. I tried the following query: select *, count(*) as cnt from table group by session having weight=cnt But the problem is HAVING is applied after the grouping. I want my condition (weight=cnt) to be processed before the grouping. I can't add it in the Where clause because of cnt. How can I do this ? Valentin Agachi _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query comparing to a DATE on Linux OpenOffice
On Tue, 2003-11-11 at 00:15, Alaios wrote: what a front-end is? And what OO1.1.0 provides? MySQL is a database server and a front-end is another program that you use to get to that server and use the database. OpenOffice is a complete office software package (word processor, spreadsheet, ...) that also has a database interface. You can check it out at www.openoffice.org -Marc Marc [EMAIL PROTECTED] wrote:I'm using OpenOffice 1.1.0 as a front-end to MySQL 4.0.14 on Gentoo Linux and can't compare a field to CURDATE(). The error is, The field cannot be compared with a date. The column is formatted as a DATE, and I can perform the compare fine using mysql. I realize this is probably related only to OpenOffice, and not a mysql problem, but I haven't gotten any answers on the OOo lists. So I'm hoping some kind soul on the mysql/myodbc lists might have experienced this and have an answer. Thanks for any ideas. -- Marc -- Marc [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with query comparing to a DATE on Linux OpenOffice
I'm using OpenOffice 1.1.0 as a front-end to MySQL 4.0.14 on Gentoo Linux and can't compare a field to CURDATE(). The error is, The field cannot be compared with a date. The column is formatted as a DATE, and I can perform the compare fine using mysql. I realize this is probably related only to OpenOffice, and not a mysql problem, but I haven't gotten any answers on the OOo lists. So I'm hoping some kind soul on the mysql/myodbc lists might have experienced this and have an answer. Thanks for any ideas. -- Marc [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query comparing to a DATE on Linux OpenOffice
what a front-end is? And what OO1.1.0 provides? Marc [EMAIL PROTECTED] wrote:I'm using OpenOffice 1.1.0 as a front-end to MySQL 4.0.14 on Gentoo Linux and can't compare a field to CURDATE(). The error is, The field cannot be compared with a date. The column is formatted as a DATE, and I can perform the compare fine using mysql. I realize this is probably related only to OpenOffice, and not a mysql problem, but I haven't gotten any answers on the OOo lists. So I'm hoping some kind soul on the mysql/myodbc lists might have experienced this and have an answer. Thanks for any ideas. -- Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
problem with query
Hi ALL! I have a problem with this query, because it keeps failing and I dont know why (it says that the concat statement is wrong but there is nothing on the online docs...) select st.id, concat(st.required), st.name from states st, agenda ag left outer join ag.id=concat(AGE,st.required) where st.type='AGE' and st.id0; in this st.required is an int(7) and ag.id is a varchar(40). Thanks Leo. Mysql sql query select, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with query
Leonardo Javier Bel? wrote: Hi ALL! I have a problem with this query, because it keeps failing and I dont know why (it says that the concat statement is wrong but there is nothing on the online docs...) select st.id, concat(st.required), st.name from states st, agenda ag left outer join ag.id=concat(AGE,st.required) where st.type='AGE' and st.id0; Did you mean: select st.id, concat(st.required), st.name from states st left join agenda ag on ag.id=concat(AGE,st.required) where st.type='AGE' and st.id0; in this st.required is an int(7) and ag.id is a varchar(40). Thanks Leo. Mysql sql query select, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
having a problem with query using multiple sources
hi.. having a problem with a mysql query where 2 different sources use the same query.. the only problem is that in my where clause i.e.: where '$edit[company]'==$company the $edit[company] is not valid for one of the sources... is there a way to let both sources use the same query like that.. my where clauses would have to be: where '$edit[company]'=$company where '$company'=$company --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.458 / Virus Database: 257 - Release Date: 2/24/2003 - 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
AW: Problem with query cache and comments
I may add, that it is always the same comment, so the query is exactly the same and SHOULD be cached. -Ursprungliche Nachricht- Von: Christian Rabe [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 12. Januar 2003 01:41 An: [EMAIL PROTECTED] Betreff: Problem with query cache and comments Hi, I've been testing the query cache with mysql 4.0.8 and ran into a problem. the query select * from tablename where x=1; is cached correctly and generates nice hits when run more than once but if I add a comment like /* any comment */ select * from tablename where x=1; the query is cached, but there are no more hits I dont think this is intended ... Please reply or CC me directly, because I'm not on the list atm. Thanx and Regards, Christian Rabe - 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
Problem with query cache and comments
Hi, I've been testing the query cache with mysql 4.0.8 and ran into a problem. the query select * from tablename where x=1; is cached correctly and generates nice hits when run more than once but if I add a comment like /* any comment */ select * from tablename where x=1; the query is cached, but there are no more hits I dont think this is intended ... Please reply or CC me directly, because I'm not on the list atm. Thanx and Regards, Christian Rabe - 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: Problem with Query based on HTML form values - Clarification
You have it right. But if the user searches for new power boats, and doesn't specify a make, make should not appear in your WHERE clause. The query has to be different depending on the criteria that have been chosen (or not chosen, as the case may be). Prior to executing the query, look at all the form fields you've gotten and ignore the ones that don't have a value attached to them. -bill web-dev wrote: Bill Lovett wrote: Hello, You're getting all new boats + all sail boats + all boats of type x because you're using ORs. If you only want records that match all the criteria, use ANDs instead. If I use AND, wouldn't all conditions need to be true to return a result? In the case of the search form as constructed, the user can select or enter search criteria information in from one to all of the available search criteria. Using the truncated criteria list as an example, if the user selected 1)new 2) power boats and left the 3) make text box empty, AND, and the whole query, fails because the WHERE make like '$make' condition returns false, I think. Please correct me if I'm wrong! I want and need to understand this.. - 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
Problem with Query based on HTML form values
Hello, I am attempting to search a table based on information passed from a submitted form. The form contains details as follows: (radio buttons) Condition: O new O used Type: O powerO sail (textbox) Make: [ ] etc. etc. etc. ( it's a long list!) If I write a query ie. Select from boat where condition like '$condition' or type like '$type' or make like '$make' etc.. I get a search result that includes boats with any of the criteria that match one of the like comparisons, ie.returns all new boats + all sail boats + all boats of type X which makes sense What I really want is a result that is limited to a group of boats that individually match the submitted criteria but excludes the boats outside the setie. returns only boats which are new, sail, type X boats. If anyone can help me with sage advice on how to go about achieving this end, I would be highly grateful. I am stuck and may be missing something simple, or maybe there is more to what I am attempting than a simple query. Either way, pointers, tips, sample queries would be helpful and appreciated. Kris O. - 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: Problem with Query based on HTML form values
Hello, You're getting all new boats + all sail boats + all boats of type x because you're using ORs. If you only want records that match all the criteria, use ANDs instead. SELECT * FROM boat WHERE condition='$condition' AND type='$type' Or you might try SELECT * FROM boat WHERE (condition='new' OR condition='used') AND type='sail' You probably don't need to use LIKE since the form fields will always supply you with the same values. -bill web-dev wrote: Hello, I am attempting to search a table based on information passed from a submitted form. The form contains details as follows: (radio buttons) Condition: O new O used Type: O powerO sail (textbox) Make: [ ] etc. etc. etc. ( it's a long list!) If I write a query ie. Select from boat where condition like '$condition' or type like '$type' or make like '$make' etc.. I get a search result that includes boats with any of the criteria that match one of the like comparisons, ie.returns all new boats + all sail boats + all boats of type X which makes sense What I really want is a result that is limited to a group of boats that individually match the submitted criteria but excludes the boats outside the setie. returns only boats which are new, sail, type X boats. If anyone can help me with sage advice on how to go about achieving this end, I would be highly grateful. I am stuck and may be missing something simple, or maybe there is more to what I am attempting than a simple query. Either way, pointers, tips, sample queries would be helpful and appreciated. Kris O. - 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
Re: Problem with Query based on HTML form values - Clarification
Bill Lovett wrote: Hello, You're getting all new boats + all sail boats + all boats of type x because you're using ORs. If you only want records that match all the criteria, use ANDs instead. If I use AND, wouldn't all conditions need to be true to return a result? In the case of the search form as constructed, the user can select or enter search criteria information in from one to all of the available search criteria. Using the truncated criteria list as an example, if the user selected 1)new 2) power boats and left the 3) make text box empty, AND, and the whole query, fails because the WHERE make like '$make' condition returns false, I think. Please correct me if I'm wrong! I want and need to understand this.. SELECT * FROM boat WHERE condition='$condition' AND type='$type' Or you might try SELECT * FROM boat WHERE (condition='new' OR condition='used') AND type='sail' You probably don't need to use LIKE since the form fields will always supply you with the same values. -bill web-dev wrote: Hello, I am attempting to search a table based on information passed from a submitted form. The form contains details as follows: (radio buttons) Condition: O new O used Type: O powerO sail (textbox) Make: [ ] etc. etc. etc. ( it's a long list!) If I write a query ie. Select from boat where condition like '$condition' or type like '$type' or make like '$make' etc.. I get a search result that includes boats with any of the criteria that match one of the like comparisons, ie.returns all new boats + all sail boats + all boats of type X which makes sense What I really want is a result that is limited to a group of boats that individually match the submitted criteria but excludes the boats outside the setie. returns only boats which are new, sail, type X boats. If anyone can help me with sage advice on how to go about achieving this end, I would be highly grateful. I am stuck and may be missing something simple, or maybe there is more to what I am attempting than a simple query. Either way, pointers, tips, sample queries would be helpful and appreciated. Kris O. - 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 - 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: Problem with query - join two tables - order by goes crazy
Since it can't use an index, and has to sort the output, what exactly is unexpected? Norris, Joseph wrote: Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. Thanks. - 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
Problem with query - join two tables - order by goes crazy
Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. Thanks. - 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
Problem with query
I am having a small problem with a small mysql query... I want to make a list of: WHO HAS NOT PAID THEIR MEMBERSHIP FEE. I guess I could maybe solve this one myself, its not too hard ... I have one table with members MEMBERS -- | MemberID | Name | etc. One table with fee payments FEE_PAYMENTS -- | MemberID | date | price | etc... Now: How would I make a query that would list all members that have not paid their fee? How would I make a query that would list all members that have paid their fee? I'm just too tired to think it out myself :) Forgive me :) - Med vennlig hilsen, Torkil Johnsen [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: Problem with query
select * from MEMBERS,FEE_PAYMENTS where FEE_PAYMENTS.PAID = '0' and MEMBERS. MemberID = FEE_PAYMENTS. MemberID; assuming you have a field PAID, with values 0 or 1,or even YES or NO cheers Mark I am having a small problem with a small mysql query... I want to make a list of: WHO HAS NOT PAID THEIR MEMBERSHIP FEE. I guess I could maybe solve this one myself, its not too hard ... I have one table with members MEMBERS -- | MemberID | Name | etc. One table with fee payments FEE_PAYMENTS -- | MemberID | date | price | etc... Now: How would I make a query that would list all members that have not paid their fee? How would I make a query that would list all members that have paid their fee? I'm just too tired to think it out myself :) Forgive me :) - Med vennlig hilsen, Torkil Johnsen [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 - 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: Problem with query
Depends on how the fee paymnents are defined. At its simplest: SELECT Members..Name FROM Members WHERE NOT(Members.MemberID = FEE_PAYMENTS.MenmbersID) would select members that have no fee payment records. You could build on it from there... For the mySQL gurus, sorry about the syntax guys, I've been with Access to long but I'm coming over quickly :-) Pierre Torkil Johnsen wrote: I am having a small problem with a small mysql query... I want to make a list of: WHO HAS NOT PAID THEIR MEMBERSHIP FEE. I guess I could maybe solve this one myself, its not too hard ... I have one table with members MEMBERS -- | MemberID | Name | etc. One table with fee payments FEE_PAYMENTS -- | MemberID | date | price | etc... Now: How would I make a query that would list all members that have not paid their fee? How would I make a query that would list all members that have paid their fee? I'm just too tired to think it out myself :) Forgive me :) - Med vennlig hilsen, Torkil Johnsen [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 -- Pierre du Parté Final Filer Software 349 Worrigee Road Worrigee, NSW, Australia 2540 http://www.finalfiler.com Phone 61 2 44216374 Mobile 0413 483 066 If it feels good, do it! - 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: SV: Problem with query
select * from MEMBERS,FEE_PAYMENTS where FEE_PAYMENTS.price '1' and MEMBERS.memberID = FEE_PAYMENTS.memberID; am I getting close? :o) Mark The FEE_PAYMENTS table does not have one line corresponding to each line in the MEMBERS table. The members table can look like this: MEMBERS ID | NAME | BIRTHDAY | etc... 1 | Art Garfunkle | 1940-05-02 | 2 | Torkil Johnsen | 1980-05-26 | 3 | Bill Clinton | 1956-02-12 | 4 | Madonna | 1962-08-02 | 5 | Rune Araber | 1954-12-17 | And the fee_payments table can look like this: FEE_PAYMENTS ID | MEMBER_ID | PRICE 1 | 2 | 500 2 | 4 | 750 So as you can see, only Torkil Johnsen and Madonna has paid their fees, 500 and 750 respectively. Now I want to list the people that have not paid their fees... How would you do this? - Torkil :) - Opprinnelig melding - Fra: Mark Dale [EMAIL PROTECTED] Til: Torkil Johnsen [EMAIL PROTECTED] Kopi: [EMAIL PROTECTED] Sendt: 17. april 2002 11:17 Emne: Re: Problem with query select * from MEMBERS,FEE_PAYMENTS where FEE_PAYMENTS.PAID = '0' and MEMBERS. MemberID = FEE_PAYMENTS. MemberID; assuming you have a field PAID, with values 0 or 1,or even YES or NO cheers Mark I am having a small problem with a small mysql query... I want to make a list of: WHO HAS NOT PAID THEIR MEMBERSHIP FEE. I guess I could maybe solve this one myself, its not too hard ... I have one table with members MEMBERS -- | MemberID | Name | etc. One table with fee payments FEE_PAYMENTS -- | MemberID | date | price | etc... Now: How would I make a query that would list all members that have not paid their fee? How would I make a query that would list all members that have paid their fee? I'm just too tired to think it out myself :) Forgive me :) - Med vennlig hilsen, Torkil Johnsen [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 - 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
Problem with query
Hi, I Have a problem with transaction controls... runing the query begin; select * from table; commit;, this query run perfectly in the shell, but in my code return error of sintax. Somebody help me? - 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: Problem with query
I don't see any code or errors. [EMAIL PROTECTED] wrote: Hi, I Have a problem with transaction controls... runing the query begin; select * from table; commit;, this query run perfectly in the shell, but in my code return error of sintax. Somebody help me? - 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
Re: Problem with query
What kind of code ? I typically send each of those in as separate queries. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 01, 2002 8:36 AM Subject: Problem with query Hi, I Have a problem with transaction controls... runing the query begin; select * from table; commit;, this query run perfectly in the shell, but in my code return error of sintax. Somebody help me? - 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
Re: Problem with query
At 10:36 -0300 2/1/02, [EMAIL PROTECTED] wrote: Hi, I Have a problem with transaction controls... runing the query begin; select * from table; commit;, this query run perfectly in the shell, but in my code return error of sintax. If you're using some kind of MySQL API within a program that you're writing you can't issue multiple statements within the same row. Issue three separate statements (and don't include the semicolons). Somebody help me? - 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: Problem with query
Hi, Getting non-empty results when comparing something with NULL seems very odd to me too. Even if it is undefined, id expect to see an error message telling me i shouldnt compare an undefined variable rather than receiving results. Why not ask someone to change things so that WHERE xx=NULL gives the same results as WHERE xx IS NULL. Or would that be an unusual thing to request. Andrew Murphy -Original Message- From: jim barchuk [mailto:[EMAIL PROTECTED]] Sent: 27 October 2001 8:32 pm To: Paul DuBois Cc: [EMAIL PROTECTED] Subject: Re: Problem with query Hi Paul! On Sat, 27 Oct 2001, Paul DuBois wrote: At 9:38 -0400 10/27/01, jim barchuk wrote: Hi Carl! Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). Well which would you prefer, false or error? Dealing with errors is annoying if not necessary. 'False' is incorrect -if- the field is allowed to contain 'nothing' because NULL means it does contain nothing. I think what Carl meant was that it was strange that the WHERE x = NULL query returned a non-empty result set. I noticed that in the original message, too. I don't know what accounts for it. I think WHERE x = NULL 'may' return non-empty results, or not, simply because NULL is undefined and most bets are off for that query. Undefined doesn't mean random. Yes, http://www.mysql.com/doc/W/o/Working_with_NULL.html does say In MySQL, 0 or NULL means false and anything else means true. In this case I take undefined to mean that what appears to work this time may not work next time, depending on the structure of the query, or even if it works consistently with one version of MySQL it may not with another. Quoting page 47 of your book, If you attempt to use NULL with the usual arithmetic comparison operators, the result is undefined. Similarly, quoting http://www.mysql.com/doc/P/r/Problems_with_NULL.html , To look for NULL values, you must use the IS NULL test. 'Must' is a very strong word and I take it as gospel. I think I have less problem with trying to understand what accounts for something that may appear odd to others is that I don't try to think about 'why' when given such clear instrutions. I had a -very- educational experience with NULL with my very first attempt at MySQL -- HTML rendering. TABLE cells that -appeared- to contain something but shouldn't have and really didn't. Had my head spinning for hours until I finally got a grip on the idea that NULL is a Very Strange Thing and that I *must* deal with it only in certain ways. NULL rocks. :) Have a :) day! jb -- jim barchuk [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 - 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: Problem with query
Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. sql, database, damned crap, grrr... - 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: Problem with query
Hi Carl! Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). Well which would you prefer, false or error? Dealing with errors is annoying if not necessary. 'False' is incorrect -if- the field is allowed to contain 'nothing' because NULL means it does contain nothing. Both 0 and are 'not nothing.' They are distinct values within their set of allowed values. NULL is a 'different' kind of value that is very very useful in many cases. For a field that is allowed to contain NULL: A query that returns NULL means essentially 'do nothing with these results.' A query that returns 0 or , without -further- tests for those values, means 'do something with these values.' When coding it's easier to 'do nothing' rather than test further and later decide you really need to 'not do something.' :) As I just posted to another message, read through the hits returned by http://www.mysql.com/doc/manual.php?search_query=null . As described there dealing with NULL requires a 'different' kind of thinking. It's like thinking about black holes or Heisenberg's box or /dev/null or other places that don't really 'exist' in our normal human plane of reference *but* do in fact exist and can be used. Have a :) day! jb ob-filter-words: database table sql -- jim barchuk [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: Problem with query
At 9:38 -0400 10/27/01, jim barchuk wrote: Hi Carl! Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). Well which would you prefer, false or error? Dealing with errors is annoying if not necessary. 'False' is incorrect -if- the field is allowed to contain 'nothing' because NULL means it does contain nothing. I think what Carl meant was that it was strange that the WHERE x = NULL query returned a non-empty result set. I noticed that in the original message, too. I don't know what accounts for it. jb ob-filter-words: database table sql -- jim barchuk [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: Problem with query
Hi Paul! On Sat, 27 Oct 2001, Paul DuBois wrote: At 9:38 -0400 10/27/01, jim barchuk wrote: Hi Carl! Paul DuBois writes: NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) It is sort of odd, though, that x = NULL returns something which appears to be undefined rather than something well-defined (such as false all the time) or an error (which could be said to be well-defined, if errors are considered to be valid responses to queries...). Well which would you prefer, false or error? Dealing with errors is annoying if not necessary. 'False' is incorrect -if- the field is allowed to contain 'nothing' because NULL means it does contain nothing. I think what Carl meant was that it was strange that the WHERE x = NULL query returned a non-empty result set. I noticed that in the original message, too. I don't know what accounts for it. I think WHERE x = NULL 'may' return non-empty results, or not, simply because NULL is undefined and most bets are off for that query. Undefined doesn't mean random. Yes, http://www.mysql.com/doc/W/o/Working_with_NULL.html does say In MySQL, 0 or NULL means false and anything else means true. In this case I take undefined to mean that what appears to work this time may not work next time, depending on the structure of the query, or even if it works consistently with one version of MySQL it may not with another. Quoting page 47 of your book, If you attempt to use NULL with the usual arithmetic comparison operators, the result is undefined. Similarly, quoting http://www.mysql.com/doc/P/r/Problems_with_NULL.html , To look for NULL values, you must use the IS NULL test. 'Must' is a very strong word and I take it as gospel. I think I have less problem with trying to understand what accounts for something that may appear odd to others is that I don't try to think about 'why' when given such clear instrutions. I had a -very- educational experience with NULL with my very first attempt at MySQL -- HTML rendering. TABLE cells that -appeared- to contain something but shouldn't have and really didn't. Had my head spinning for hours until I finally got a grip on the idea that NULL is a Very Strange Thing and that I *must* deal with it only in certain ways. NULL rocks. :) Have a :) day! jb -- jim barchuk [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
Problem with query
I am having a strange problem with a query... I have a table that contains 3 columns and 3,315,599 rows. One of the columns is a date time field. Out of 3,315,599 rows 1,555,157 contain an entry in the date time field and the other 1,760,442 rows contain null in the date time field Here is the problem I will illustrate it with queries and results. Illustrates the number of rows in the table mysql select count(compid) from dbLastFaxed ; +---+ | count(compid) | +---+ | 3315599 | +---+ 1 row in set (0.00 sec) illustrates the number of rows with dates in the lastFaxedDateTime field mysql select count(compid) from dbLastFaxed where lastFaxedDateTime '2001-12-01 00:00' ; +---+ | count(compid) | +---+ | 1555157 | +---+ 1 row in set (7.84 sec) illustrates the number of rows with a null value in the lastFaxedDateTime field mysql select count(compid) from dbLastFaxed where lastFaxedDateTime = null ; +---+ | count(compid) | +---+ | 1760442 | +---+ 1 row in set (11.05 sec) Huh? Shouldn't this query yield 3,315,599 (1,555,157+1,760,442) Whats going on here? mysql select count(compid) from dbLastFaxed where lastFaxedDateTime '2001-12-01 00:00' or lastFaxedDateTime =null; +---+ | count(compid) | +---+ | 1555157 | +---+ 1 row in set (8.29 sec) - 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: Problem with query
hi. first of many replies, i'm sure: mysql select count(compid) from dbLastFaxed where lastFaxedDateTime IS null ; when you're looking for NULL fields, select where something IS NULL, instead of '=' -Original Message- From: Brendin [mailto:[EMAIL PROTECTED]] Sent: October 26, 2001 5:46 PM To: [EMAIL PROTECTED] Subject: Problem with query I am having a strange problem with a query... I have a table that contains 3 columns and 3,315,599 rows. One of the columns is a date time field. Out of 3,315,599 rows 1,555,157 contain an entry in the date time field and the other 1,760,442 rows contain null in the date time field Here is the problem I will illustrate it with queries and results. - 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: Problem with query
Yours is the first and the winner! :) Thanks using is null worked in the query... Do you know why = doesn't work? -Original Message- From: Ravi Raman [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 4:00 PM To: Mysql; Brendin Subject: RE: Problem with query hi. first of many replies, i'm sure: mysql select count(compid) from dbLastFaxed where lastFaxedDateTime IS null ; when you're looking for NULL fields, select where something IS NULL, instead of '=' -Original Message- From: Brendin [mailto:[EMAIL PROTECTED]] Sent: October 26, 2001 5:46 PM To: [EMAIL PROTECTED] Subject: Problem with query I am having a strange problem with a query... I have a table that contains 3 columns and 3,315,599 rows. One of the columns is a date time field. Out of 3,315,599 rows 1,555,157 contain an entry in the date time field and the other 1,760,442 rows contain null in the date time field Here is the problem I will illustrate it with queries and results. - 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: Problem with query
At 15:54 -0600 10/26/01, Brendin wrote: Yours is the first and the winner! :) Thanks using is null worked in the query... Do you know why = doesn't work? NULL basically means unknown value, so saying WHERE x = NULL cannot work, even if x is NULL. That means where one unknown value = another unknown value, which cannot be evaluated with any certainty. :-) - 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
