I have the following SQL which works fine …
select count(*) from invoice left join account on invoice.fkIDWithAccountID = account.id left join company on invoice.fkIDWithCompanyID = company.id where invoice.deleted = 0 and (select if((select if(invoice.applyDiscount, if(invoice.discountType, sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) - invoice.discountAmount, sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) - (sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) / 100) * invoice.discountPercent), sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent)) from invoice_line_item left join tax_rate_template on invoice_line_item.fkIDWithTaxRateTemplateID = tax_rate_template.id where invoice.id = invoice_line_item.fkIDWithInvoiceID) <= (select sum(amount) from invoice_payment where invoice.id = invoice_payment.fkIDWithInvoiceID), 0, 1)) like '%1%' and account.deleted = 0;
The problem is, if I add "company.deleted = 0" on the end, it just gives a reading of 0 for the count(*)
select count(*) from invoice left join account on invoice.fkIDWithAccountID = account.id left join company on invoice.fkIDWithCompanyID = company.id where invoice.deleted = 0 and (select if((select if(invoice.applyDiscount, if(invoice.discountType, sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) - invoice.discountAmount, sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) - (sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) / 100) * invoice.discountPercent), sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent)) from invoice_line_item left join tax_rate_template on invoice_line_item.fkIDWithTaxRateTemplateID = tax_rate_template.id where invoice.id = invoice_line_item.fkIDWithInvoiceID) <= (select sum(amount) from invoice_payment where invoice.id = invoice_payment.fkIDWithInvoiceID), 0, 1)) like '%1%' and account.deleted = 0 and company.deleted = 0;
All of the records for company have deleted = 0 so this should not be a problem. Any ideas anyone? Why would it suddenly break by adding "company.deleted = 0" on the end?
Are you sure that the problem is last condition ‘company.deleted = 0’ ?
Debug displaying company.deleted field in the first sql, replacing
select count(*) from invoice left join account...
with
select company.deleted from invoice left join account...
Not sure I have changed it a bit now. The code is below …
SELECT COUNT(*) FROM `invoice` LEFT JOIN `company` ON `invoice`.`fkIDWithCompanyID` = `company`.`id` LEFT JOIN `account` ON `company`.`fkIDWithAccountID` = `account`.`id` WHERE (((`invoice`.deleted=0) and account.id = 1) AND ((select if((select if(invoice.applyDiscount, if(invoice.discountType, sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) - invoice.discountAmount, sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) - (sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) / 100) * invoice.discountPercent), sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent)) from invoice_line_item left join tax_rate_template on invoice_line_item.fkIDWithTaxRateTemplateID = tax_rate_template.id where invoice.id = invoice_line_item.fkIDWithInvoiceID) <= (select sum(amount) from invoice_payment where invoice.id = invoice_payment.fkIDWithInvoiceID), 0, 1)) LIKE '%1%') AND (`company`.deleted=0)) AND (`account`.deleted=0)
If I remove "and account.id = 1" it works fine, but if I put "and account.id = 1", it just gives count 0 which is wrong.
There is nothing wrong with account.id = 1 either, because account is 1 etc.
To add to this, I have the following SQL …
SELECT COUNT(*) FROM `invoice` LEFT JOIN `company` ON `invoice`.`fkIDWithCompanyID` = `company`.`id` LEFT JOIN `account` ON `company`.`fkIDWithAccountID` = `account`.`id` WHERE ((((`invoice`.deleted=0) AND (`account`.`id`=1)) AND ((select if((select if(invoice.applyDiscount, if(invoice.discountType, sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) - invoice.discountAmount, sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) - (sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent) / 100) * invoice.discountPercent), sum(invoice_line_item.cost + (invoice_line_item.cost / 100) * tax_rate_template.percent)) from invoice_line_item left join tax_rate_template on invoice_line_item.fkIDWithTaxRateTemplateID = tax_rate_template.id where invoice.id = invoice_line_item.fkIDWithInvoiceID) <= (select sum(amount) from invoice_payment where invoice.id = invoice_payment.fkIDWithInvoiceID), 0, 1)) = 0)) AND (`company`.deleted=0)) AND (`account`.deleted=0)
If I put " = 0" I get 33 records which is correct.
If I put " = 0 or 1" I get 92 records which is correct.
If I put " = 1" I get 0 records, which is incorrect.
If I put " = 1 or 0" I get 0 records, which is incorrect.
