问题:
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 invo...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
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";
回答1:
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.
回答2:
If you want result also when invoice_items have no value yous hould 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 sinta based on where and use explicit join sintax the also let you use left join (outer join)
you should also avoid the use of php var in you sql code .. you are at risk for sqlinjection .. for this take a look at your db driver for prpared statement and bindig param