php/mysql query
Posted: Wed Nov 30, 2011 6:58 pm
Hi everyone,
I have a complex query which works fine if I omit the payments table, but as soon as I include it, I receive the following error message:
every derived table must have its own alias. I'm not sure where to assign the aliases. Does anyone have any ideas for me?
I have a complex query which works fine if I omit the payments table, but as soon as I include it, I receive the following error message:
every derived table must have its own alias. I'm not sure where to assign the aliases. Does anyone have any ideas for me?
Code: Select all
<?php
$result = mysql_query("SELECT DISTINCT Min(tblprivatepracticedetails.Date_Of_Entry) AS MinOfDate_Of_Entry, Max(tblprivatepracticedetails.Date_Of_Entry) AS MaxOfDate_Of_Entry, tblprivatepractice.startdate, tblprivatepractice.enddate, tblprivatepractice.inv_num, tblprivatepractice.InvoiceDate, tblprivatepractice.ServiceAmount, tblprivatepractice.AmountPaid, tblprivatepractice.AmountRemaining, Sum(tblprivatepractice.bill_rate*tblprivatepractice.bill_time+tblprivatepractice.travl_time*tblprivatepractice.travl_rate+tblprivatepractice.milage*tblprivatepractice.mileage_rate+tblprivatepractice.expenserate*tblprivatepractice.expenseqty) AS LineTotal, tblprivatepractice.SalesTaxRate, SumOfTotalPayments AS TotalPayments, tblprivatepractice.id_number, tblprivatepractice.LatePaymentRate, Sum(tblprivatepracticedetails.InvoiceSubmitted) AS SumOfInvoiceSubmitted FROM (tblprivatepractice LEFT JOIN (SELECT tblpayments.Inv_num, Sum(tblpayments.TotalPayments) AS SumOfTotalPayments FROM (tblclient INNER JOIN tblprivatepractice ON tblclient.ID_Number = tblprivatepractice.id_number) INNER JOIN tblpayments ON tblprivatepractice.inv_num = tblpayments.Inv_num GROUP BY tblpayments.Inv_num) ON tblprivatepractice.inv_num = tblpayments.Inv_num) INNER JOIN tblprivatepracticedetails ON tblprivatepractice.inv_num = tblprivatepracticedetails.inv_num GROUP BY tblprivatepractice.inv_num ORDER BY Min(tblprivatepracticedetails.Date_Of_Entry) DESC
") or die(mysql_error());
echo $result;
?>