title: Join two tables to get the number of total access for user in MySql
body: <p>In my MySql I have two tables: <code>tblN1</code> and <code>tblN2</code>.</p>
  
  <p>The table <code>tblN1</code> is an old table where are memorized account access in restricted area on the website.</p>
  
  <p>The table <code>tblN2</code>is a new table where are memorized account access in restricted area on the website.</p>
  
  <p>If try this query on <code>tblN1</code> :</p>
  
  <pre><code>mysql&gt; SELECT
      COUNT(*) AS TotalN1
  FROM
      `tblN1`
  WHERE
      UserN1 IN ('7047505')
  AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
  AND CURDATE();
  +---------+
  | TotalN1 |
  +---------+
  |       4 |
  +---------+
  1 row in set
  </code></pre>
  
  <p>The return of this query is <code>4</code>.</p>
  
  <p>If try this query on <code>tblN2</code> :</p>
  
  <pre><code>mysql&gt; SELECT
      COUNT(*) as TotalN2
  FROM
      `tblN2`
  WHERE
      UserN2 IN ('7047505')
  AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
  AND CURDATE();
  +---------+
  | TotalN2 |
  +---------+
  |       0 |
  +---------+
  1 row in set
  </code></pre>
  
  <p>The return of this query is <code>0</code>.</p>
  
  <p>I'm trying to join two tables to get the number of total access for user.</p>
  
  <pre><code>mysql&gt; SELECT
      (
          COUNT(DISTINCT A.ID) + COUNT(DISTINCT CA.ID)
      ) AS SuperTotal,
      COUNT(DISTINCT A.ID) AS TotalN1,
      COUNT(DISTINCT CA.ID) AS TotalN2,
      UserN1 AS UserN1,
      UserN2  AS UserN2
  FROM
      `tblN1` A
  JOIN `tblN2` CA ON A.UserN1 = CA.UserN2
  WHERE
      CA.UserN2 IN ('7047505')
  AND (
      dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
      AND CURDATE()
      AND `dateN2` BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
      AND CURDATE()
  );
  +------------+---------+---------+--------+--------+
  | SuperTotal | TotalN1 | TotalN2 | UserN1 | UserN2 |
  +------------+---------+---------+--------+--------+
  |          0 |       0 |       0 | NULL   | NULL   |
  +------------+---------+---------+--------+--------+
  1 row in set
  </code></pre>
  
  <p>The return is <code>0</code> for all fields, why?</p>
  
  <p>Please help me, thank you in advance.</p>
  
  <p><strong>Edit #1</strong></p>
  
  <pre><code>mysql&gt; SELECT sum(total) as Total FROM
      (SELECT COUNT(*) AS Total
      FROM `tblN1`
      WHERE UserN1 IN ('7047505')
            AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1) AND CURDATE()
      UNION ALL
      SELECT COUNT(*)
      FROM `tblN2`
      WHERE UserN2 IN ('7047505')
            AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1) AND CURDATE()
      ) AS t;
  
  
  +-------+
  | Total |
  +-------+
  | 0     |
  +-------+
  1 row in set
  </code></pre>