Возможны ли это «оплаченные или неоплаченные счета» MySQL-запрос?

Я пытаюсь создать запрос, который будет отображать «оплаченные» счета. Мой запрос не работает, что означает, что в нем есть либо ошибка, либо то, что я пытаюсь достичь, невозможно таким образом.

// paid $statement = "accounts_invoice i INNER JOIN customer_type ct on i.invoice_customer_type = ct.customer_type_id LEFT JOIN accounts_invoice_payment ip on i.invoice_id = ip.invoice_payment_invoice_id WHERE invoice_posted='1' AND (i.invoice_total_amount_exc_vat + i.invoice_total_vat_amount) = ip.TotalPayments" . $where . " ORDER BY invoice_id DESC"; $invoice_details_query = mysqli_query($con, "SELECT i.*, ct.customer_type_name, ip.invoice_payment_amount AS TotalPayments FROM {$statement} LIMIT {$startpoint} , {$per_page}") or die(mysql_error());

Строка $where в этом экземпляре пуста, вы можете удалить ее, если хотите. Вы также можете удалить «LIMIT», поскольку это чисто используется для разбивки на страницы.

Я просто пытаюсь сделать страницу, чтобы отображать оплаченные или неоплаченные счета-фактуры, и это сводит меня с ума, учитывая, что это звучит так просто в теории!

Структура базы данных;

 -- phpMyAdmin SQL Dump -- version 4.0.10.7 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 08, 2015 at 05:17 PM -- Server version: 5.1.73-cll -- PHP Version: 5.4.31 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; -- -- Database: `propsyst_atlas` -- -- -------------------------------------------------------- -- -- Table structure for table `accounts_invoice` -- CREATE TABLE IF NOT EXISTS `accounts_invoice` ( `invoice_id` int(11) NOT NULL AUTO_INCREMENT, `invoice_customer_type` tinyint(4) DEFAULT NULL, `invoice_customer` int(11) DEFAULT NULL, `invoice_date` date DEFAULT NULL, `invoice_due_date` date DEFAULT NULL, `invoice_property_id` int(11) DEFAULT NULL, `invoice_tenancy_id` int(11) DEFAULT NULL, `invoice_branch` int(11) DEFAULT NULL, `invoice_payment_terms` tinyint(4) DEFAULT NULL, `invoice_notes` text COLLATE utf8_bin, `invoice_total_amount_exc_vat` decimal(10,2) DEFAULT NULL, `invoice_total_vat_amount` decimal(10,2) DEFAULT NULL, `invoice_posted` tinyint(4) DEFAULT '0', `invoice_date_created` datetime DEFAULT NULL, `invoice_date_updated` datetime DEFAULT NULL, `invoice_date_posted` datetime DEFAULT NULL, `invoice_created_by` int(11) DEFAULT NULL, `invoice_updated_by` int(11) DEFAULT NULL, `invoice_posted_by` int(11) DEFAULT NULL, PRIMARY KEY (`invoice_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=87 ; -- phpMyAdmin SQL Dump -- version 4.0.10.7 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 08, 2015 at 05:18 PM -- Server version: 5.1.73-cll -- PHP Version: 5.4.31 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; -- -- Database: `propsyst_atlas` -- -- -------------------------------------------------------- -- -- Table structure for table `accounts_invoice_payment` -- CREATE TABLE IF NOT EXISTS `accounts_invoice_payment` ( `invoice_payment_id` int(11) NOT NULL AUTO_INCREMENT, `invoice_payment_date` date DEFAULT NULL, `invoice_payment_amount` decimal(10,2) DEFAULT NULL, `invoice_payment_method` tinyint(4) DEFAULT NULL, `invoice_payment_invoice_id` int(11) DEFAULT NULL, `invoice_payment_notes` text COLLATE utf8_bin, `invoice_payment_date_created` datetime DEFAULT NULL, `invoice_payment_date_updated` datetime DEFAULT NULL, `invoice_payment_created_by` int(11) DEFAULT NULL, `invoice_payment_updated_by` int(11) DEFAULT NULL, PRIMARY KEY (`invoice_payment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=71 ; -- phpMyAdmin SQL Dump -- version 4.0.10.7 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 08, 2015 at 05:58 PM -- Server version: 5.1.73-cll -- PHP Version: 5.4.31 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; -- -- Database: `propsyst_atlas` -- -- -------------------------------------------------------- -- -- Table structure for table `customer_type` -- CREATE TABLE IF NOT EXISTS `customer_type` ( `customer_type_id` int(11) NOT NULL AUTO_INCREMENT, `customer_type_name` varchar(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`customer_type_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;

Эффективно ваш запрос

 SELECT i.*, ip.invoice_payment_amount AS TotalPayments FROM accounts_invoice i LEFT JOIN accounts_invoice_payment ip ON i.invoice_id = ip.invoice_payment_invoice_id WHERE invoice_posted='1' AND (i.invoice_total_amount_exc_vat + i.invoice_total_vat_amount) = ip.TotalPayments ORDER BY invoice_id DESC

и этот запрос терпит неудачу, потому что ip.TotalPayments в инструкции where является неизвестным столбцом. Вам нужно использовать оригинальное имя ip.invoice_payment_amount . Псевдоним используется только в возвращаемом наборе, а не в самом запросе.

Кроме того, можно было бы улучшить дизайн базы данных. Сначала я бы посоветовал использовать InnoDB вместо MyISAM . Это позволяет использовать внешние ключи, поэтому между таблицами существует соотношение.

Также я бы переместил часть AND из WHERE предложение ON , например:

 SELECT i.*, ip.invoice_payment_amount AS TotalPayments FROM accounts_invoice i LEFT JOIN accounts_invoice_payment ip ON i.invoice_id = ip.invoice_payment_invoice_id AND (i.invoice_total_amount_exc_vat + i.invoice_total_vat_amount) = ip.invoice_payment_amount WHERE invoice_posted='1' ORDER BY invoice_id DESC

Результат SELECT * FROM A LEFT JOIN B ON A.id = B.a_id содержит каждую строку A и соответствующие строки в B Если B не имеет соответствующей строки для строки в A , значения столбцов в B равны NULL . Если вы этого не хотите, но вместо этого хотите, чтобы строки A имели соответствующую строку в B , вы должны использовать INNER JOIN .