Page 1 of 1

php/mysql query

Posted: Wed Nov 30, 2011 6:58 pm
by cjkeane
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?

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;
?>

Re: php/mysql query

Posted: Wed Nov 30, 2011 7:50 pm
by twinedev
Looking at this now, for others, here is the query grouped a little more readable (tabs still screw up here, but better than all one long line)
[text]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

[/text]

Re: php/mysql query

Posted: Wed Nov 30, 2011 8:11 pm
by twinedev
Try the following:

Code: Select all

SELECT

    DISTINCT MIN(ppd.Date_Of_Entry) AS MinOfDate_Of_Entry,
    MAX(ppd.Date_Of_Entry) AS MaxOfDate_Of_Entry,
    pp1.startdate,
    pp1.enddate,
    pp1.inv_num,
    pp1.InvoiceDate,
    pp1.ServiceAmount,
    pp1.AmountPaid,
    pp1.AmountRemaining,
    SUM(pp1.bill_rate * pp1.bill_time
            + pp1.travl_time * pp1.travl_rate
            + pp1.milage * pp1.mileage_rate
            + pp1.expenserate * pp1.expenseqty
         ) AS LineTotal,
    pp1.SalesTaxRate,
    SumOfTotalPayments AS TotalPayments,
    pp1.id_number,
    pp1.LatePaymentRate,
    Sum(ppd.InvoiceSubmitted) AS SumOfInvoiceSubmitted

FROM
    (
            tblprivatepractice AS pp1
        LEFT JOIN
            (
                SELECT
                    pmts.Inv_num,
                    Sum(pmts.TotalPayments) AS SumOfTotalPayments
                FROM
                    (
                            tblclient
                        INNER JOIN
                            tblprivatepractice AS pp2
                        ON
                            tblclient.ID_Number = pp2.id_number
                    )
                INNER JOIN
                    tblpayments AS pmts
                ON
                    pp2.inv_num = pmts.Inv_num

                GROUP BY
                    pmts.Inv_num
            )
        ON
            pp1.inv_num = pmts.Inv_num
    )

INNER JOIN
    tblprivatepracticedetails AS ppd
ON
    pp1.inv_num = ppd.inv_num

GROUP BY
    pp1.inv_num

ORDER BY
    MIN(ppd.Date_Of_Entry) DESC
If that doesn't give the results you are expecting, try changing the following line: [text]pp2.inv_num = pmts.Inv_num[/text] to [text]pp1.inv_num = pmts.Inv_num[/text]

-Greg

Re: php/mysql query

Posted: Thu Dec 01, 2011 11:47 am
by cjkeane
i've tried the revised code, however i still receive: Every derived table must have its own alias.
do you have any other ideas?

Re: php/mysql query

Posted: Sat Dec 03, 2011 5:46 am
by twinedev
Forgot the alias on the actual internal select I think (where I added sPmt):

Code: Select all

SELECT

    DISTINCT MIN(ppd.Date_Of_Entry) AS MinOfDate_Of_Entry,
    MAX(ppd.Date_Of_Entry) AS MaxOfDate_Of_Entry,
    pp1.startdate,
    pp1.enddate,
    pp1.inv_num,
    pp1.InvoiceDate,
    pp1.ServiceAmount,
    pp1.AmountPaid,
    pp1.AmountRemaining,
    SUM(pp1.bill_rate * pp1.bill_time
            + pp1.travl_time * pp1.travl_rate
            + pp1.milage * pp1.mileage_rate
            + pp1.expenserate * pp1.expenseqty
         ) AS LineTotal,
    pp1.SalesTaxRate,
    SumOfTotalPayments AS TotalPayments,
    pp1.id_number,
    pp1.LatePaymentRate,
    Sum(ppd.InvoiceSubmitted) AS SumOfInvoiceSubmitted

FROM
    (
            tblprivatepractice AS pp1
        LEFT JOIN
            (
                SELECT
                    pmts.Inv_num,
                    Sum(pmts.TotalPayments) AS SumOfTotalPayments
                FROM
                    (
                            tblclient
                        INNER JOIN
                            tblprivatepractice AS pp2
                        ON
                            tblclient.ID_Number = pp2.id_number
                    )
                INNER JOIN
                    tblpayments AS pmts
                ON
                    pp2.inv_num = pmts.Inv_num

                GROUP BY
                    pmts.Inv_num
            ) AS sPmt
        ON
            pp1.inv_num = sPmt.Inv_num
    )

INNER JOIN
    tblprivatepracticedetails AS ppd
ON
    pp1.inv_num = ppd.inv_num

GROUP BY
    pp1.inv_num

ORDER BY
    MIN(ppd.Date_Of_Entry) DESC

Re: php/mysql query

Posted: Sat Dec 03, 2011 1:01 pm
by cjkeane
thanks for the help, however the error i'm receiving now is Resource id #7. any other ideas?
Disregard that. The query runs fine in myphpadmin. The only issue now is that it doesn't calculate the total payments correctly.