Using MySQL to compare two fields inside the table based on a primary key and a differing field

Question

Quick Question about optimization / easier way of approaching a problem.

I have a table of invoices with the fields [companyID, invoiceid,date,amount,status]

I'm looking to find all the differences between two invoices (for the same company) of different dates to see if the amount changed at all.

What I'm currently doing I think could be better optimized or brought down to one or two queries but I'm not certain how to do so or if I'm just searching the wrong things.

Current Code:

$getPreviousInvoices = mysql_query("SELECT `accountid`,`amount` FROM `invoices` WHERE `date` = '$dateFrom'",$db);



while($previousInvoice = mysql_fetch_array($getPreviousInvoices))

{

    $accountID = $previousInvoice['accountid'];

    $getNewInvoice = mysql_query("SELECT `amount` FROM `invoices` WHERE `date` = '$dateTo' AND `accountid` = '$accountID'",$db);



    if($newInvoice = mysql_fetch_array($getNewInvoice))

    {

        $newAmount = $newInvoice['amount'];

        $oldAmount = $previousInvoice['amount'];

        if($newAmount != $oldAmount)

        {

            echo"{$accountID} : {$oldAmount} changed to {$newAmount} \n";



        }

    }



}

The obvious issue here is that I'm having to poll the DB every time for each of the other date's invoices. I was thinking a JOIN would work here but I'm not sure how to do a JOIN with a different query on a table with itself based on a different amount? (If that makes sense)

Thanks for any help you guys can give!

Answer 1

i think you can do it with one self join. you may try following query

SELECT a.`accountid`, a.`amount`, b.`amount` ,a.`date`,b.`date` 
FROM `invoices` as a, `invoices` as b 
WHERE a.accountid = b.accountid 
AND a.`date` = '$dateFrom' AND b.date = '$dateTo'
AND a.`amount` <> b.`amount`

Using MySQL to compare two fields inside the table based on a primary key and a differing field

Sign up