List:General Discussion« Previous MessageNext Message »
From:Becoming Digital Date:June 14 2003 11:12pm
Subject:Re: Impossible query??
View as plain text  
Your query seems relatively easy if you don't need the first test (in this case,
testID 2) explicitly printed.

mysql> SELECT * FROM tests WHERE testID<=7 AND connect>0;
+---------------------+----------+----------+
| testId              | connect  | result   |
+---------------------+----------+----------+
| 7                   | 6        | ok       |
| 6                   | 4        | nok      |
| 4                   | 3        | nok      |
| 3                   | 2        | nok      |
+---------------------+----------+----------+

The history of testID 2 is implied by your table structure, such that you know
testID 2 passed because it is the last "connect" value.

The problem I see with this occurs when you have a "repeating" data set.  A
query for your next failure (after testID 7) will also return testID 7 and its
history, as would be the case with the below data.  Repairing this would require
adding an additional parameter to the WHERE statement, probably using BETWEEN.
+---------------------+----------+----------+
| testId              | connect  | result   |
+---------------------+----------+----------+
| 1                   | 0        | ok       |
| 2                   | 0        | nok      |
| 3                   | 2        | nok      |
| 4                   | 3        | nok      |
| 5                   | 0        | ok       |
| 6                   | 4        | nok      |
| 7                   | 6        | ok       |
| 8                   | 0        | ok       |
| 9                   | 0        | nok      |
| 10                  | 9        | nok      |
| 11                  | 10       | ok       |
+---------------------+----------+----------+


Edward Dudlik
Becoming Digital
www.becomingdigital.com


----- Original Message -----
From: "trashMan" <trashman@stripped>
To: <mysql@stripped>
Sent: Saturday, 14 June, 2003 11:28
Subject: Impossible query??


Hi, sorry for my bad english.

I've a table where i store the result of a quality test.
If the test is passed then the result=ok and connect=0
If test fail then result=nok and, if it's the first test for the
product, connect=0
When i retest a failed product i've  connect= previous testid of the
product failed
It's possible to do a query for print the history of one id??

Exemple

mysql> select * from mytable;

+---------------------+----------+----------+
| testId              | connect  | result   |
+---------------------+----------+----------+
| 1                   | 0        | ok       |
| 2                   | 0        | nok      |
| 3                   | 2        | nok      |
| 4                   | 3        | nok      |
| 5                   | 0        | ok       |
| 6                   | 4        | nok      |
| 7                   | 6        | ok       |
| 8                   | 0        | ok      |
+---------------------+----------+----------+


I want the history of  testId=7
mysql>  ??????

+---------------------+----------+----------+
| testId              | connect  | result   |
+---------------------+----------+----------+
| 7                   | 6        | ok       |
| 6                   | 4        | nok      |
| 4                   | 3        | nok      |
| 3                   | 2        | nok      |
| 2                   | 0        | ok       |
+---------------------+----------+----------+


Thanks a lot!

Max (trashman)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1




Thread
converting currenciesDavid Garamond16 Apr
  • Impossible query??trashMan14 Jun
    • Re: Impossible query??Becoming Digital15 Jun
      • RE: Impossible query??Michael Scott15 Jun