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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
