> -----Mensagem original-----
> De: SGreen@stripped [mailto:SGreen@stripped]
> "pedro mpa" <mail.pmpa@stripped> wrote on 01/31/2006 12:49:48 AM:
> > Greetings.
> > I need help on the following query.
> > I get an error like "Unknown/Invalid column total_price [...]" when I
> > filter by total_price.
> > How can I do this correctly?
> > SELECT receipts.*,
> > (SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE
> > receipt_itens.id_receipt=receipts.id) AS total_price
> > FROM receipts
> > WHERE
> > total_price >= ".$minprice." "
> > AND total_price <= ".$maxprice." "
> > ORDER BY receipts.date DESC
> > Thanks in advance.
> > Pedro.
> You don't need a subquery to get these results. You can also use a regular
> low-tech JOIN:
> CREATE TEMPORARY TABLE tmpDateTotals SELECT
> , sum(receipt_itens.price) total_price
> FROM receipts
> LEFT JOIN receipt_itens
> ON receipts.id = receipt_itens.id_receipt
> HAVING total_price >= ".$minprice."
> AND total_price <= ".$maxprice."
> GROUP BY receipts.date;
> SELECT receipts.*, dt.total_price
> FROM receipts
> INNER JOIN tmpDateTotals dt
> ON dt.date = receipts.date
> ORDER BY receipts.date desc;
> DROP TEMPORARY TABLE tmpDateTotals;
> The problem with your original query was that you were trying to compare
> the results of a calculation in your WHERE clause. Results do not exist
> when WHERE clauses are evaluated but they do by the time the HAVING
> clauses are checked. The column`total_price` only exists _after_ the SUM()
> function is computed which happens _after_ the restrictions of any ON and
> WHERE clauses are applied to your source data. The HAVING clause is
> specifically designed to handle this type of comparison. I will bet
> dollars to doughnuts that your subquery version of this query will execute
> 5 to 10 times slower than my version without the subquery. Can you please
> try both and report your results?
Thank you for your good explanation.
The query I wrote is an example of a more complex query I am building in
which I use a lot of subquerys in detriment of JOIN clause. When I finish
rebuilding the query I will compare performance and post it.
I didn't know that subquerys are much slower than JOINS.