title: Mysql inner join on two subqueries returning duplicate rows
body: <p>We have a table like,</p>
  
  <pre><code>Table1(Contract_id, name, address, contact_no)
  </code></pre>
  
  <p>and another table like,</p>
  
  <pre><code>Table2(Contract_id, approver, owner, authority)
  </code></pre>
  
  <p>Sample data:</p>
  
  <pre><code>mysql&gt; select * from Table1;
  +-------------+-------+---------+------------+
  | contract_id | owner | address | contact_no |
  +-------------+-------+---------+------------+
  |       11111 | XXX   | Madurai | 897161     |
  |       12456 | XYZ   | Madras  | 897161     |
  |       11111 | XYZ   | Madras  | 897161     |
  +-------------+-------+---------+------------+
  3 rows in set (0.00 sec)
  
  mysql&gt; select * from Table2;
  +-------------+----------+
  | contract_id | approver |
  +-------------+----------+
  |       11111 | YZX      |
  |       11112 | YYY      |
  +-------------+----------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>I have written a query to get all contract_ids and matching data for a criteria like this,</p>
  
  <p>"Get all contracts with Owner like 'X' and address like "Mad" and approver  = 'YZX'"</p>
  
  <pre><code>select contract_id,owner,address,approver 
  from 
  (
      select *
      from Table1 
      where owner like '%X%' 
      and address like '%Mad%'
  ) t1 
  inner join 
  (
      select * 
      from Table2 
      where approver = 'YZX'
  ) t2 using (contract_id);
  </code></pre>
  
  <p>It is returning the results correctly. But the problem is left table has two matching rows and right table has only one matching row. So the row in right table is duplicated twice.</p>
  
  <pre><code>&gt; +-------------+---------------+-----------+-------------+
  &gt; |contract_id  |  owner        | address   | approver    |
  &gt; +-------------+---------------+-----------+-------------+
  &gt; |11111        | XXX           | Madurai   | YZX         |
  &gt; +-------------+---------------+-----------+-------------+
  &gt; |11111        | XYZ           | Madras    | YZX         | 
  &gt; +-------------+---------------+-----------+-------------+
  </code></pre>
  
  <p>The approver value is duplicated twice. Can i somehow avoid this mysql itself?
  I want null value for the second row's approver column.</p>
  
  <p>EDIT 1: </p>
  
  <p>I edited my query to have group by approver at the end,</p>
  
  <pre><code>select contract_id,owner,address,approver 
  from 
  (
      select contract_id
      from Table1 
      where owner like '%X%' 
      and address like '%Mad%'
  ) t1 
  inner join 
  (
      select contract_id 
      from Table2 
      where approver = 'YZX'
  ) t2 using (contract_id) group by approver;
  </code></pre>
  
  <p>Now the results became like,</p>
  
  <pre><code>&gt; +-------------+---------------+-----------+-------------+
  &gt; |contract_id  |  owner        | address   | approver    |
  &gt; +-------------+---------------+-----------+-------------+
  &gt; |11111        | XYZ           | Madurai   | YZX         |
  &gt; +-------------+---------------+-----------+-------------+
  </code></pre>
  
  <p>The second went missing now. I want that as well. </p>
  
  <p>EDIT 2: Added the sample data and exact queries.</p>
  
  <p>EDIT 3: I want the results to be formatted like the below,</p>
  
  <pre><code>&gt; +-------------+---------------+-----------+-------------+
  &gt; |contract_id  |  owner        | address   | approver    |
  &gt; +-------------+---------------+-----------+-------------+
  &gt; |11111        | XXX           | Madurai   | YZX         |
  &gt; +-------------+---------------+-----------+-------------+
  &gt; |11111        | XYZ           | Madras    | NULL        | 
  &gt; +-------------+---------------+-----------+-------------+
  </code></pre>
  
  <p>EDIT 4: I acheived what I wanted. Below is the query I used.</p>
  
  <pre><code>create temporary table table1 select * 
  from 
  (
      select *
      from Table1 
      where owner like '%X%' 
      and address like '%Mad%'
  ) t1 
  inner join 
  (
      select * 
      from Table2 
      where approver = 'YZX'
  ) t2 using (contract_id) limit 1;
  </code></pre>
  
  <p>and the query,</p>
  
  <pre><code>insert into table1 select contract_id, IF((select count(*) from table1 where owner = t.owner and contract_id = t.contract_id) &gt; 0, NULL, t.contract_id),IF((select count(*) from table1 where address = t.adress and contract_id = t.contract_id) &gt; 0, NULL, t.contract_id),IF((select count(*) from table1 where approver = t.approver and contract_id = t.contract_id) &gt; 0, NULL, t.contract_id)
  from 
  ((
      select *
      from Table1 
      where owner like '%X%' 
      and address like '%Mad%'
  ) t1 
  inner join 
  (
      select * 
      from Table2 
      where approver = 'YZX'
  ) t2 using (contract_id)) t;  
  </code></pre>