Hello.
On Thu, Jun 28, 2001 at 10:48:23PM +0200, [EMAIL PROTECTED] wrote:
[...]
> the user inserts some wished dates (the lowest score, the searched
> planet may have, the highest, the highest number of roids and the
> lowest..) and the number-interval in wich the _galaxy-score_ may be
> included. (means x > galaxy-sore > y). the problem is, to query
> that! the first query is very simple: "SELECT * FROM Planetarion
> WHERE Score > $x AND Score < $y AND Size > $a AND Size < $B". how
> how you get the galaxie-score for that?? the second query alone is
> very simple, too: "SELECT SUM(Score) as Summe FROM $table WHERE X =
> $x AND Y = $y GROUP BY X, Y".
>
> _But how to combine both queries???_
I think the following is what you are looking for.
SELECT X, Y,
SUM(Score) AS Summe
SUM(Size) AS Groesse
FROM Planetarion
GROUP BY X, Y
HAVING Summe > $sizemin AND Summe < $sizemax AND
Groesse > $groessemin AND Groesse < $groessemax
> i tried getting the first one and process for each elemnt of it the
> second one... but then the resources of my server will get low, when
> the first query results eg. 300 elements or more....
The above query is also quite resource intensive, simply because it
has to visit all rows to calculate the SUMs, before it can apply the
HAVING clause.
Another approach is, if it is feasable in your situation, to create a
summary table with the result of the calculation (of course, that
makes only sense if there are times, where the data doesn't change),
and then only query the summary table (which will be pretty fast in
comparison):
INSERT INTO TABLE plan_sum (<types and indexes)
SELECT X, Y,
SUM(Score) AS Summe
SUM(Size) AS Groesse
FROM Planetarion
GROUP BY X, Y
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
