I don't think that solves the problem. There are multiple test chains with
Id's less than 7.
ie
7->6->4->3->2
5
1
and your query looking for history on testId=7
SELECT * FROM tests WHERE testID<=7 AND connect>0;
could return testId's 5 and 1 as well if they were part of longer chains
What is needed (and does not exist) is a recursive query that can be started
on a given testId, follows the connect->testId chain and terminates when it
reaches a record with connect=0
A single query can not do this. You will be forced to solve this
programatically by executing a query that selects a single record based on
the previous record's connect field until you reach a connect=0. (Caution,
if ever a connect value points back into the chain you will enter an endless
loop!)
Or
Re-Design your table (and inserting code ) by adding a column called
baseTestId that stores the original testId. Every time a new test is added
that is an extension of a previous test the previous testId is copied into
the new record and the previous baseTestId is also copied to the new record
baseTestID. (If a test is the first one it uses it's own id as the
baseTestID).
Your sample table would look like this...
assuming three chains exist
7->6->4->3->2
5
1
+---------------------+----------+------------+----------+
| testId | connect | baseTestId | result |
+---------------------+----------+------------+----------+
| 1 | 0 | 1 | ok |
| 2 | 0 | 2 | nok |
| 3 | 2 | 2 | nok |
| 4 | 3 | 2 | nok |
| 5 | 0 | 5 | ok |
| 6 | 4 | 2 | nok |
| 7 | 6 | 2 | ok |
| 8 | 0 | 8 | ok |
+---------------------+----------+------------|----------+
note how all the records that are in a chain have the same baseTestID!
Then this single query will do what you want
SELECT B.* FROM myTable AS A
LEFT OUTER JOIN myTable AS B ON(A.baseTesID=B.baseTestID)
WHERE (A.testId=7)
It returns
+--------+---------+------------+----------+
| testId | connect | baseTestId | result |
+--------+---------+------------+----------+
| 2 | 0 | 2 | nok |
| 3 | 2 | 2 | nok |
| 4 | 3 | 2 | nok |
| 6 | 4 | 2 | nok |
| 7 | 6 | 2 | ok |
+--------+---------+------------+----------+
The idea here is that you need a single piece of data that can relate all
the records in a chain of tests together. The where clause selects the test
Id in question and the join collects all records whose baseTestID is the
same as the record selected by the where clause.
This query would return all tests in the chain no matter which id you used.
If you queried on testId 4 the same set of data would be returned, basically
all tests in the chain.
Now if you already have tons of data in place you will need to update the
table data so that the baseTestID field reflects these new rules.
Mike Scott, Ind. Contractor
-----Original Message-----
From: Becoming Digital [mailto:[EMAIL PROTECTED]
Sent: Saturday, June 14, 2003 4:13 PM
To: [EMAIL PROTECTED]
Subject: Re: Impossible query??
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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
--
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]
