How to change sql query with my requirement?

Question

In my sql database,I have two tables one is Invoices and another table called invoiceitems.So am fetching customer details from invoices(invoices table) along with the invoice items(from invoice item table). I am facing one problem in my sql code.Datas are not fetching if invoice have no items in the invoiceitems table.

I want to fetch all datas including if invoices have no items in the invoice item table. Below sql code only generating the invoices have the invoiceitems. So can anyone help me to do this??

$sql = "SELECT 
        a.cusName, 
        a.cusMob, 
        a.invoiceNo,
        a.invoiceDate, 
        a.total_VAT, 
        a.bill_tot,
        b.itemsName ,
        b.rate, 
        b.amt_vat,
        ROUND(b.amt_vat + b.amount, 2) as amount
    FROM 
        invoices a, 
        invoice_items b 
    where 
        a.invoiceID=b.invoiceid
        and a.invoiceDate between '$getfromdate' and '$gettodate' 
        and a.status IS NULL 
    order by 
        a.invoiceID desc";

score 3 · Accepted Answer · answered Mar 24 '19 at 19:31

You should try and change your SQL to the newer JOIN notation as it will make this sort of thing a lot simpler, then you can use a LEFT JOIN to indicate this is an optional table...

SELECT a.cusName, a.cusMob, a.invoiceNo,
       a.invoiceDate, a.total_VAT, a.bill_tot,
       b.itemsName ,b.rate, b.amt_vat,
       ROUND(b.amt_vat + b.amount, 2) as amount
  FROM invoices a
  LEFT JOIN invoice_items b ON a.invoiceID=b.invoiceid
  WHERE a.invoiceDate between '$getfromdate' and '$gettodate' 
      and a.status IS NULL 
  order by a.invoiceID desc

I would also recommend that you look at using prepared statements as they have a lot of advantages.

score 2 · Answer 2 · edited Jan 21 '22 at 21:48

If you want result also when invoice_items have no value, you should use left join and if null:

$sql    = "SELECT a.cusName
                , a.cusMob
                , a.invoiceNo
                , a.invoiceDate
                , a.total_VAT
                , a.bill_tot
                , b.itemsName 
                , b.rate
                , b.amt_vat
                , ROUND(b.amt_vat + inull(b.amount,0), 2) as amount
            FROM invoices a
            LEFT JOIN invoice_items b ON a.invoiceID=b.invoiceid
            WHERE a.invoiceDate between '$getfromdate' 
                and '$gettodate' and a.status IS NULL 
            order by a.invoiceID desc";

You should also avoid old implicit join syntax based on where and use explicit join syntax. This also lets you use a left join (outer join).

You should also avoid the use of PHP var in your SQL code. You are at risk for SQL injection. For this take a look at your db driver for prepared statement and bound parameters.

How to change sql query with my requirement?

2 Answers2