php - How to check in another table has no data while doing mysql join?

I have two mysql table.

Invoice_details

invoice_no   proje_ID invoice_Date invoice_amount
1004             6
1005             4
1006             2
1007             2
1008             1
1009             7

received_amount_details 

invoice_no        received_amount
1004                 205.00
1004                  40.00
1006                 283.50

Expected ouput: select from invoice_details where proje_ID =6
invoice no        invoice_amount   invoice_Date  received_amount
1004                                                  245.00

Expected ouput: select from invoice_details where proje_ID =2
invoice no        invoice_amount   invoice_Date  received_amount
1006                                                   283.50
1007                                                    0.00 (becoz 1007 invoice is not there in another table called received_amount_details so show total sum 0.00)

Expected ouput: select from invoice_details where proje_ID =7
    invoice no        invoice_amount   invoice_Date  received_amount
    1009                                                  0.00
Copy code

Here is my query:

public function many($id) {
   $sql = "SELECT invoice_details.invoice_No, invoice_details.invoice_Date, invoice_details.invoice_amount,
        SUM(received_amount_details.received_amount) as totalreceiptamt
        FROM 
          (SELECT DISTINCT invoice_no, received_amount FROM received_amount_details) AS received_amount_details 
      
         INNER JOIN invoice_details ON received_amount_details.invoice_no = invoice_details.invoice_No
         where invoice_details.proje_ID='7'
         group by invoice_details.invoice_No";

        return $this->db->query($sql)->result();
            }
Copy code

currently am getting an empty result if I select invoice details where proje_ID='7' Where i have to change in my php code?

When I select an invoice (invoice no:1004) it will display all the invoice details and sum of the total received_amount (205.00 + 40.00).But if select invoice (invoice no:1007) am getting null data becoz this 1007 invoice no is not there in second table. If it is not there in the received_amount_details table just return the invoice details and total received_amount =0.00. (I want to display the invoice details where project id =2 and total received_amount =0.00.)

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.