title: mySql subtract row of different table
body: <p>I want to subtract between two rows of different table:</p>
  
  <p>I have created a <code>view</code> called <code>leave_taken</code> and <code>table</code> called <code>leave_balance</code>.</p>
  
  <p>I want this result from both table:</p>
  
  <pre><code>leave_taken.COUNT(*) - leave_balance.balance
  </code></pre>
  
  <p>and group by <code>leave_type_id_leave_type</code></p>
  
  <p><strong>Code of both table</strong></p>
  
  <p>-----------------View Leave_Taken-----------</p>
  
  <pre><code>CREATE ALGORITHM = UNDEFINED DEFINER=`1`@`localhost` SQL SECURITY DEFINER 
  VIEW `leave_taken` 
  AS 
     select 
       `leave`.`staff_leave_application_staff_id_staff` AS `staff_leave_application_staff_id_staff`,
       `leave`.`leave_type_id_leave_type` AS `leave_type_id_leave_type`,
       count(0) AS `COUNT(*)` 
     from 
     (
        `leave` 
        join `staff` on((`staff`.`id_staff` = `leave`.`staff_leave_application_staff_id_staff`))
     ) 
     where (`leave`.`active` = 1) 
     group by `leave`.`leave_type_id_leave_type`;
  </code></pre>
  
  <p>----------------Table leave_balance----------</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `leave_balance` (
    `id_leave_balance` int(11) NOT NULL AUTO_INCREMENT,
    `staff_id_staff` int(11) NOT NULL,
    `leave_type_id_leave_type` int(11) NOT NULL,
    `balance` int(3) NOT NULL,
    `date_added` date NOT NULL,
    PRIMARY KEY (`id_leave_balance`),
    UNIQUE KEY `id_leave_balance_UNIQUE` (`id_leave_balance`),
    KEY `fk_leave_balance_staff1` (`staff_id_staff`),
    KEY `fk_leave_balance_leave_type1` (`leave_type_id_leave_type`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
  </code></pre>
  
  <p>------- Table leave ----------</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `leave` (
    `id_leave` int(11) NOT NULL AUTO_INCREMENT,
    `staff_leave_application_id_staff_leave_application` int(11) NOT NULL,
    `staff_leave_application_staff_id_staff` int(11) NOT NULL,
    `leave_type_id_leave_type` int(11) NOT NULL,
    `date` date NOT NULL,
    `active` int(11) NOT NULL DEFAULT '1',
    `date_updated` date NOT NULL,
    PRIMARY KEY (`id_leave`,`staff_leave_application_id_staff_leave_application`,`staff_leave_application_staff_id_staff`),
    KEY `fk_table1_leave_type1` (`leave_type_id_leave_type`),
    KEY `fk_table1_staff_leave_application1` (`staff_leave_application_id_staff_leave_application`,`staff_leave_application_staff_id_staff`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;
  </code></pre>