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