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

```
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