List:General Discussion« Previous MessageNext Message »
From:pedro mpa Date:January 31 2006 5:10pm
Subject:Help on Unknown field error
View as plain text  
> -----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
> try
> > 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
>         receipts.date
>         , 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.

Thank you.

Pedro.


Thread
Help on Unknown field errorpedro mpa31 Jan
  • Re: Help on Unknown field errorMichael Stassen31 Jan
  • Re: Help on Unknown field errorSGreen31 Jan
    • Help on Unknown field errorpedro mpa31 Jan