![]() Insert invoice (custId,invAmt) values (101,100) - 2 Insert invoice (custId,invAmt) values (101,9999) - 1 NOTE you need foreign key (FK) constraints on your real table It will drag tremendously on even medium size data sets. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=71 `invoice_payment_updated_by` int(11) DEFAULT NULL, `invoice_payment_created_by` int(11) DEFAULT NULL, `invoice_payment_date_updated` datetime DEFAULT NULL, `invoice_payment_date_created` datetime DEFAULT NULL, ![]() `invoice_payment_notes` text COLLATE utf8_bin, `invoice_payment_invoice_id` int(11) DEFAULT NULL, `invoice_payment_method` tinyint(4) DEFAULT NULL, `invoice_payment_amount` decimal(10,2) DEFAULT NULL, `invoice_payment_date` date DEFAULT NULL, `invoice_payment_id` int(11) NOT NULL AUTO_INCREMENT, ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=87 ĬREATE TABLE IF NOT EXISTS `accounts_invoice_payment` ( `invoice_posted_by` int(11) DEFAULT NULL, `invoice_updated_by` int(11) DEFAULT NULL, `invoice_created_by` int(11) DEFAULT NULL, `invoice_date_posted` datetime DEFAULT NULL, `invoice_date_updated` datetime DEFAULT NULL, `invoice_date_created` datetime DEFAULT NULL, `invoice_total_vat_amount` decimal(10,2) DEFAULT NULL, `invoice_total_amount_exc_vat` decimal(10,2) DEFAULT NULL, `invoice_payment_terms` tinyint(4) DEFAULT NULL, `invoice_tenancy_id` int(11) DEFAULT NULL, `invoice_property_id` int(11) DEFAULT NULL, `invoice_customer_type` tinyint(4) DEFAULT NULL, `invoice_id` int(11) NOT NULL AUTO_INCREMENT, Table structure CREATE TABLE IF NOT EXISTS `accounts_invoice` ( ![]() I did have a go at the query myself (see Is this 'paid or unpaid invoices' MySQL query possible?) but I failed to mention that there can be more than one payment against an invoice. An invoice can also have no payments made against it meaning the full balance is outstanding. For example, a £100 invoice can be paid using any number of payments for varying amounts. An invoice can have more than one payment made against it. One to display a list of paid invoices and the other to display unpaid invoices. Basically, using MySQL/PHP, I need to construct two queries. I have two tables, please see their structure below. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |