Q:

Is this 'paid or unpaid invoices' MySQL query possible?

I'm trying to produce a query that will display 'paid' invoices. My query isn't working which means there's either an error in it or what I'm trying to achieve is impossible in this way.
// 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());

The $where string is empty in this instance, you can delete it if you wish. You can also remove the 'LIMIT' as this is purely being used for pagination.
I'm simply trying to make a page to display paid or unpaid invoices and it's driving me mad considering it sounds so simple in theory!
Database structure;
-- 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 ;

A:

Effectively your query is
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

and that query fails because the ip.TotalPayments in the where statement is an unknown column. You need to use the original name ip.invoice_payment_amount. The alias is only used in the returned set, not in the query itself.
Also, your database design could be improved. First I would advise to use InnoDB instead of MyISAM. That allows you to use foreign keys, so there's actually a relation between the tables.
Also I would move the AND part of the WHERE clause to the ON clause, like so:
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

The result of SELECT * FROM A LEFT JOIN B ON A.id = B.a_id contains every row of A and the matching rows of B. If B does not have a matching row for a row in A, the values for the columns in B are NULL. If you do not want that, but instead only want the rows of A that have a matching row in B, you should use INNER JOIN.

