MYSQL Query to get invoice items from invoice table using INNER JOIN-CodePudding

I have two tables. I want to extract data from two tables tbl_invoice and tbl_invoices_details.

First table

Second Hand

I have tried but it's not working.

    SELECT * 
      FROM tbl_invoice 
INNER JOIN tbl_invoice_details 
        ON tbl_invoice.invoice_id=tbl_invoice_details.invoice_id   
     WHERE tbl_invoice.invoice_id="4"
     GROUP by department_name

Alternatively, I have a query that gets all the invoices for a particular department in a particular month.

SELECT * 
  FROM tbl_invoice 
 WHERE department_name="DIFA" 
   AND order_date BETWEEN '2021-08-01' AND '2021-10-13'

but not sure how to get the individual items as list in those invoices.

CodePudding user response：

An example of group by :

SELECT expression1, expression2, ... expression_n,   
aggregate_function (expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;

TO learn more . Please visit here

For your case :

SELECT * FROM tbl_invoice INNER JOIN tbl_invoice_details ON tbl_invoice.invoice_id=tbl_invoice_details.invoice_id WHERE tbl_invoice.invoice_id="4" GROUP by department_name

Here (*) of your select statement needs to be replaced with department_name and your desired aggragate_functions (example - count,sum etc). And separated by comma.

If you just want to join then remove GROUP BY . Fetch the result tbl_invoice.field_name or tbl_invoice_details.field_name.

It should work this way.