title: SQL 'merging' columns from result sets into one result set
body: <p>I've been pulling my hair about how to write a particular view within the constraints of MySQL.</p>
  
  <p>The following tables and columns are of importance:</p>
  
  <pre><code>CREATE TABLE `invoices` (
    `id` int(10) unsigned NOT NULL AUTO INCREMENT,
    PRIMARY KEY (`id`)
  )
  
  -- Joins payments to invoices. The sum of all `invoice_currency_value`s is the balance paid towards an invoice.
  CREATE TABLE `financial_transactions_invoices` (
    `id` int(10) unsigned NOT NULL AUTO INCREMENT,
    `invoice` int(10) unsigned NOT NULL,
    `invoice_currency_value` decimal(8,2) unsigned NOT NULL,
    PRIMARY KEY (`id`)
  )
  
  -- Lists items (services) available to purchase.
  CREATE TABLE `items` (
    `id` int(10) unsigned NOT NULL AUTO INCREMENT,
    `value` decimal(8,2) unsigned NOT NULL
    PRIMARY KEY (`id`)
  )
  
  -- Each instance represents that the `item` has been purchased.
  CREATE TABLE `item_instances` (
    `id` int(10) unsigned NOT NULL AUTO INCREMENT,
    `invoice` int(10) unsigned NOT NULL,
    `item` int(10) unsigned NOT NULL,
    `invoice_currency_rate` decimal(11,5) unsigned NOT NULL,
    PRIMARY KEY (`id`)
  )
  
  -- Any number of tax instances can exist for an item instance and indicate this tax has been applied to the associated item instance.
  CREATE TABLE `tax_instances` (
    `id` int(10) unsigned NOT NULL AUTO INCREMENT,
    `item_instance` int(10) unsigned NOT NULL,
    `value` decimal(8,2) unsigned NOT NULL,
    PRIMARY KEY (`id`)
  )
  </code></pre>
  
  <p>Now, I need a view that lists for each row,</p>
  
  <ul>
  <li><strong>the invoice number</strong></li>
  <li><strong>the total value of the invoice</strong></li>
  <li><strong>the total tax on the invoice</strong></li>
  <li><strong>and the total value of payments made towards the invoice</strong></li>
  </ul>
  
  <p>However, I can't figure out how to get these three separate queries into the same result set of one row per invoice, e.g.</p>
  
  <pre><code>inv_no  total_value     total_tax       payments
  1       150             5               120
  2       120             10              20
  3       10              0               10
  4       1000            150             1150
  </code></pre>
  
  <p>I have written the following query which produces the desired result, but due to the 'no subquery' rule in MySQL views, it is not acceptable.</p>
  
  <pre><code>SELECT `invoice_id`, SUM(`total_value`) AS `total_value`, SUM(`total_tax`) AS `total_tax`,
      SUM(`paid_balance`) AS `paid_balance`
  FROM
  (SELECT `invoices`.`id` AS `invoice_id`, SUM(`items`.`value` * `item_instances`.`invoice_currency_rate`) AS `total_value`,
      NULL AS `total_tax`, NULL AS `paid_balance`
  FROM `items`
      JOIN `item_instances` ON `items`.`id` = `item_instances`.`item`
      JOIN `invoices` ON `item_instances`.`invoice` = `invoices`.`id`
  GROUP BY `invoices`.`id`
  UNION
  SELECT `invoices`.`id`, NULL, SUM(`tax_instances`.`value`), NULL
  FROM `tax_instances`
      JOIN `item_instances` ON `tax_instances`.`item_instance` = `item_instances`.`id`
      JOIN `invoices` ON `item_instances`.`invoice` = `invoices`.`id`
  GROUP BY `invoices`.`id`
  UNION
  SELECT `invoices`.`id`, NULL, NULL, SUM(`financial_transactions_invoices`.`invoice_currency_value`)
  FROM `financial_transactions_invoices`
      JOIN `invoices` ON `financial_transactions_invoices`.`invoice` = `invoices`.`id`
  GROUP BY `invoices`.`id`) AS `components`
  GROUP by `invoice_id`;
  </code></pre>
  
  <p>Without tackling the problem in the way I have, I can't think of any other way I can do it <strong>within MySQL</strong>.</p>
  
  <p>Any ideas? Appreciate any help.</p>