List: | General Discussion | « Previous MessageNext Message » | |

From: | Lew E. Lefton | Date: | May 31 2006 11:25am |

Subject: | Need help querying a database of polynomials | ||

View as plain text |

Hi, I hope this is an appropriate place to ask this question, if you think it is better suited for another list/forum, please let me know. I have a table that looks like this: mysql> select polynomial_id, term_id from polynomial; +---------------+---------+ | polynomial_id | term_id | +---------------+---------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 1 | | 2 | 2 | | 2 | 3 | | 2 | 4 | | 2 | 5 | | 3 | 1 | | 3 | 2 | | 3 | 3 | | 3 | 5 | +---------------+---------+ which represents, say, three polynomials, the first is a sum of 4 terms (term1 + term2 + term3 + term4), the second is a sum of 5 terms (term1 + term2 + term3 + term4 + term5), the third is a sum of 4 terms (term1 + term2 + term3 + term5), etc. I am storing the polynomials in this way because I may need to store very large polynomials. The table may grow to millions of rows before I'm done, with potentially many of the same terms appearing in many different polynomials. Thus I have the terms stored in a separate table. Also, this method of storage makes the table easily searched (e.g. "find all polynomials which have term 2"). If I have another polynomial, say the sum of terms 1,3,4, and 5, how can I quickly search this database to see if it's already been stored? Actually, I would eventually like to have a function (using appropriate API) which, when given a list of terms, returns the polynomial_id regardless of whether it is the result of a new insert or a successful lookup. I tried variations of this SELECT DISTINCT polynomial_id FROM polynomial WHERE term_id in ('1','2','3','4') but I get +---------------+ | polynomial_id | +---------------+ | 1 | | 2 | | 3 | +---------------+ when I really just wanted '1'. I suspect some subquery magic (e.g. restricting to only those polynomials with exactly a count of 4 terms) would give me a nice efficient solution, but I am not an SQL expert and I have not been succesful in crafting the appropriate query. Thanks in advance for your help. I am happy to provide more details if necessary, but I have tried to distill the essence of the problem by this simple example. Cheers, Lew Lefton -- --------------------------------------------------------------------- |Lew Lefton, IT Director | Phone: (404) 385-0052 | |School of Mathematics | FAX: (404) 894-4409 | |Georgia Institute of Technology | e-mail: llefton@stripped| |Atlanta, GA 30332-0160 | http://www.math.gatech.edu/~llefton| ---------------------------------------------------------------------