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
