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

Thread
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