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|
---------------------------------------------------------------------
```
name 'Szczech' returns more rows then 'Szczec%'Lukasz Budnik31 May
• Need help querying a database of polynomialsLew E. Lefton31 May
• Re: Need help querying a database of polynomialsPeter Brawley31 May
• Re: Need help querying a database of polynomialsPooly31 May
• Re: Need help querying a database of polynomialsMichael Stassen1 Jun
• Re: name 'Szczech' returns more rows then 'Szczec%'sheeri kritzer31 May
• Re: name 'Szczech' returns more rows then 'Szczec%'Lukasz Budnik31 May
• Re: name 'Szczech' returns more rows then 'Szczec%'Jake Peavy1 Jun
• Re: name 'Szczech' returns more rows then 'Szczec%'Brendan Bouffler1 Jun
• Re: name 'Szczech' returns more rows then 'Szczec%'Lukasz Budnik1 Jun
• Re: name 'Szczech' returns more rows then 'Szczec%'Jake Peavy1 Jun
• Re: name 'Szczech' returns more rows then 'Szczec%'Remo Tex1 Jun
• Re: name 'Szczech' returns more rows then 'Szczec%'Lukasz Budnik1 Jun
• Re: name 'Szczech' returns more rows then 'Szczec%'Remo Tex1 Jun
• Re: name 'Szczech' returns more rows then 'Szczec%'Lukasz Budnik1 Jun
• myisamchk (was: name 'Szczech' returns more rows then 'Szczec%')Chris Sansom4 Jun
• Re: myisamchk (was: name 'Szczech' returns more rows then'Szczec%')Chris Sansom5 Jun
• Re: myisamchk (was: name 'Szczech' returns more rows then 'Szczec%')gclark5 Jun
• Re: myisamchk (was: name 'Szczech' returns more rows then 'Szczec%')Chris Sansom8 Jun