RE: Impossible query??
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 connect0; 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 connect0; +-+--+--+ | 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
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 connect0; +-+--+--+ | 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]
RE: Impossible Query?
I don't think it's impossible. It might be nicer if you could use a sub-select. But here's join. SELECT DISTINCT affiliates.id, DISTINCT sales.client_id,SUM(sales.client_id) FROM affiliates,clients,sales WHERE affiliates.id = clients.affiliate_id AND sales.client_id = clients.id; Would this do it? That should tell you how many sales each affiliate has made to each client. Sum that up, and you will get what you probably want in the end - a total referral, but at the least it should show you how the join would go. Benjamen R. Meyer -Original Message- From: Daren Cotter [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 11, 2002 5:03 PM To: [EMAIL PROTECTED] Subject: Impossible Query? I have three tables, affiliates, clients, and sales. The affiliates table stores all of the information about affiliates, clients about clients, sales about sales. In the clients table, there is a field for affiliate_id (affiliates refer clients), and in the sales table there is a field for client_id. I need a query that will show me a list of all affiliates and the number of sales each affiliate has generated. I know this will involve a left join, but I can't figure it out, since it involves that third table. Which actually brings up another question: would be be better to store the affiliate_id in the sales table? The reason I do it this way, is because if an affiliate refers a client, and the client is involved in numerous sales, the affiliate should be credited each time. TIA! - 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: Impossible Query?
On 11 Jul 2002, at 17:41, Witness wrote: SELECT DISTINCT affiliates.id, DISTINCT sales.client_id,SUM(sales.client_id) FROM affiliates,clients,sales WHERE affiliates.id = clients.affiliate_id AND sales.client_id = clients.id; I don't think that's what Daren wanted. For one thing, I doubt it's meaningful to sum client IDs. Maybe something more like this: SELECT a.id as affiliate_id, COUNT(s.id) as sales_count FROM affiliates a LEFT JOIN clients c ON a.id = c.affiliate_id LEFT JOIN sales s ON c.id = s.client_id GROUP BY a.id; [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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
