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

From: | Peter Brawley | Date: | May 31 2006 3:20pm |

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

View as plain text |

Lew, >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? SELECT DISTINCT polynomial_id FROM polynomial p1 INNER JOIN polynomial p2 ON p1.term_id=1 AND p2.term_id=3 INNER JOIN polynomial p3 ON p2.term_id=3 AND p3.term_id=4 INNER JOIN polynomial p4 ON p3.term_id=4 AND p4.term_id=5 PB ----- > 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 > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date: 5/31/2006