I tried using LIKE \"%published%\" and even LIKE \"%ub%\" The results are the same as when I attempt = \"published\"
Something in the query processing works differently when = is used in the WHERE. I can see no explanation in the data stored in the table. John Hughes --- Jeff Kilbride <[EMAIL PROTECTED]> wrote: > If your condition that the status field only contains two values > holds true, > then there should be no difference in the two queries you have > below -- that > I can see, unless someone can prove me blind... > > Are you *absolutely* sure none of the columns contain extra spaces, > nulls, > etc...? You might try changing your where condition to WHERE status > LIKE > \"%published%\" for the first and WHERE status LIKE \"%input%\" for > the > second -- just to see if there are any other funky characters > causing > problems. If the modified where conditions return the correct > results, > there's something wrong with the data. > > --jeff > > ----- Original Message ----- > From: "John Hughes" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Saturday, March 02, 2002 12:32 AM > Subject: Query structure puzzle > > > > I have a table with approximately 600 rows. Two of the fields are > > "record_date" and "status" Every record has an entry in the > > "record_date" in 0000-00-00 format. The "status" is either > "input" > > or "published" (I DID NOT create this colum as ENUM; those are > simply > > the only values my PHP interface provides.) > > > > What I want to do is count the number of "published" letters > grouped > > by date. We publish letters seven days a week, so there are no > days > > when there would be no "published" letters. > > > > So, here is the puzzle. These two queries serve up dramatically > > different results. The only difference in the query is the WHERE > > statement. > > > > $sql = "SELECT record_date, COUNT(status) AS number_of_letters > > FROM $table_name > > WHERE status = \"published\" > > GROUP BY record_date > > "; > > > > > > $sql = "SELECT record_date, COUNT(status) AS number_of_letters > > FROM $table_name > > WHERE status <> \"input\" > > GROUP BY record_date > > "; > > > > The query WHERE status = \"published\" finds 17 rows. > > > > The query WHERE status <> \"input\" finds 33 rows. > > > > The 33 row answer is the correct answer. I checked the individual > > records and can find no record with an answer other than "input" > or > > "published" in the status. > > > > ASSUMING all records are either "input" or "published" and all > > records have valid record_date, WHY does <> status give a > different > > result than = status? > > > > John Hughes > > > > RESULTS of WHERE status = "published" > > Date Number > > 2002-01-30 10 > > 2002-01-31 12 > > 2002-02-01 8 > > 2002-02-02 30 > > 2002-02-03 8 > > 2002-02-04 1 > > 2002-02-05 8 > > 2002-02-06 10 > > 2002-02-07 9 > > 2002-02-08 19 > > 2002-02-09 30 > > 2002-02-10 8 > > 2002-02-11 10 > > 2002-02-12 10 > > 2002-02-16 2 > > 2002-02-27 1 > > 2002-03-03 8 > > > > > > RESULTS of WHERE status <> "input" > > Date Number > > 2002-01-30 10 > > 2002-01-31 12 > > 2002-02-01 8 > > 2002-02-02 30 > > 2002-02-03 8 > > 2002-02-04 11 > > 2002-02-05 8 > > 2002-02-06 10 > > 2002-02-07 9 > > 2002-02-08 20 > > 2002-02-09 30 > > 2002-02-10 8 > > 2002-02-11 10 > > 2002-02-12 10 > > 2002-02-13 11 > > 2002-02-14 9 > > 2002-02-15 9 > > 2002-02-16 33 > > 2002-02-17 11 > > 2002-02-18 9 > > 2002-02-19 9 > > 2002-02-20 19 > > 2002-02-21 10 > > 2002-02-22 9 > > 2002-02-23 32 > > 2002-02-24 8 > > 2002-02-25 19 > > 2002-02-27 10 > > 2002-02-28 9 > > 2002-03-01 9 > > 2002-03-02 29 > > 2002-03-03 8 > > 2002-03-04 10 > > > > > > __________________________________________________ > > Do You Yahoo!? > > Yahoo! Sports - sign up for Fantasy Baseball > > http://sports.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!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.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
