Calculation between three different tables in databases?

I am a beginner in programming.

There are three tables is in database: tblclients, tblinvoices, tblinvoicepaymentrecords.

Now, I want to show Total Due in Client Area, right now there is only Percentage Graph showing.

Here is the Flow:

1. I added a client, it goes in tblclients with client id 1.

2. I create invoice of 10000 for Client ID 1, It will Record in tblinvoices with the id,total amount and client id in respective columns (id, total and clientid ).

3. Now I will record payment for this invoice, the client paid only 7000 – This payment will be recorded in tblinvoicepaymentrecords with amount paid and invoice id in respective columns ( amount and invoiceid ).

Now I want to show the client when they will be login to their customer panel, total Amount Due ( for each Client their own Due ).

I will just include this where I want to show:

Answer

SQL query:

SELECT
    client.name AS client_name,
    tblinvoices.total_amount AS total_amount,
    tblinvoicepaymentrecords.amount_paid AS paid_amount,
    (
        tblinvoices.total_amount - tblinvoicepaymentrecords.amount_paid
    ) AS amount_due
FROM
    client
LEFT JOIN tblinvoices ON client.id = tblinvoices.client_id
LEFT JOIN tblinvoicepaymentrecords ON tblinvoices.invoice_id = tblinvoicepaymentrecords.invoiceid

Here is the result I got:

As per your table structure, this will be your query:

SELECT 
tblclients.userid as client_id, 
    tblclients.company as company_name, 
        tblinvoices.total AS total_amount, 
            tblinvoicepaymentrecords.amount AS paid_amount, 
                ( tblinvoices.total - tblinvoicepaymentrecords.amount ) AS amount_due 
            FROM 
            tblclients 
            LEFT JOIN tblinvoices ON tblclients.userid = tblinvoices.clientid 
            LEFT JOIN tblinvoicepaymentrecords ON tblinvoices.id = tblinvoicepaymentrecords.invoiceid