title: Can't make an UNION of a LEFT and RIGHT JOIN  work. (trying to emulate an outer join)
body: <h3><code>At the bottom of the post you can find a simplify query of what I am trying to do</code></h3>
  <p>I am trying to create a query that shows the productivity for all the users in our <code>TRAC</code> system. I need to know all the comments that they entered (unique per ticket per week), the number of ticket touched, the number of closed tickets, and the number of revisions (next I will want to add also the number of files in such revisions checking the nodes).
  I created a query that works almost as I need, as long as the person touches at least a ticket during that week.  I have users committing revisions without referring tickets (wrong, I know, but I need to catch them too) and I tried to add the <code>RIGHT JOIN</code> and an <code>UNION</code> to emulate the <code>OUTER JOIN</code>  that MySQL doesn't support, and not I am getting this error:</p>
  <p><code>1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't LEFT JOIN ( SELECT CONCAT( ' at line 65</code></p>
  <p>If I comment out one of the two parts of the <code>UNION</code> it works.</p>
  <p>I already tried to give different aliases to the tables</p>
  <pre><code>SELECT
     t.`author` __group__,
     t.`month-week`,
     t.changes,
     t.tickets,
     t.closed,
     r.`revisions`
  FROM
  (
     (
        SELECT
           CONCAT(
              MONTH(FROM_UNIXTIME(tc.`time`/1000000)),
              '-',
              WEEK(FROM_UNIXTIME(tc.`time`/1000000))
           ) `month-week`,
           COUNT(IF(tc.`field` = &quot;comment&quot;,tc.ticket,NULL)) `changes`,
           COUNT(DISTINCT tc.`ticket`) `tickets`,
           COUNT(DISTINCT IF(tc.`field` = &quot;status&quot; AND tc.`newvalue` = &quot;closed&quot;,tc.`ticket`,NULL)) `closed`,
           tc.`author`
        FROM `ticket_change` tc
        WHERE
           tc.`time` &gt; UNIX_TIMESTAMP(NOW()-INTERVAL 2 MONTH)*1000000
        GROUP BY
           tc.`author`,
           YEARWEEK(FROM_UNIXTIME(tc.`time`/1000000))
     ) `t`
     LEFT JOIN
     (
        SELECT
           CONCAT(
              MONTH(FROM_UNIXTIME(r.`time`/1000000)),
              '-',
              WEEK(FROM_UNIXTIME(r.`time`/1000000))
           ) `month-week`,
           COUNT(DISTINCT r.`rev`) `revisions`,
           r.`author`
        FROM `revision` r
        WHERE
           r.`time` &gt; UNIX_TIMESTAMP(NOW()-INTERVAL 2 MONTH)*1000000
        GROUP BY
           r.`author`,
           YEARWEEK(FROM_UNIXTIME(r.`time`/1000000))
     ) `r` ON `t`.`month-week` = `r`.`month-week` AND `t`.`author` = `r`.`author`
  )
  UNION
  (
     (
        SELECT
           CONCAT(
              MONTH(FROM_UNIXTIME(tc.`time`/1000000)),
              '-',
              WEEK(FROM_UNIXTIME(tc.`time`/1000000))
           ) `month-week`,
           COUNT(IF(tc.`field` = &quot;comment&quot;,tc.ticket,NULL)) `changes`,
           COUNT(DISTINCT tc.`ticket`) `tickets`,
           COUNT(DISTINCT IF(tc.`field` = &quot;status&quot; AND tc.`newvalue` = &quot;closed&quot;,tc.`ticket`,NULL)) `closed`,
           tc.`author`
        FROM `ticket_change` tc
        WHERE
           tc.`time` &gt; UNIX_TIMESTAMP(NOW()-INTERVAL 2 MONTH)*1000000
        GROUP BY
           tc.`author`,
           YEARWEEK(FROM_UNIXTIME(tc.`time`/1000000))
     ) `t`
     LEFT JOIN
     (
        SELECT
           CONCAT(
              MONTH(FROM_UNIXTIME(r.`time`/1000000)),
              '-',
              WEEK(FROM_UNIXTIME(r.`time`/1000000))
           ) `month-week`,
           COUNT(DISTINCT r.`rev`) `revisions`,
           r.`author`
        FROM `revision` r
        WHERE
           r.`time` &gt; UNIX_TIMESTAMP(NOW()-INTERVAL 2 MONTH)*1000000
        GROUP BY
           r.`author`,
           YEARWEEK(FROM_UNIXTIME(r.`time`/1000000))
     ) `r` ON `t`.`month-week` = `r`.`month-week` AND `t`.`author` = `r`.`author`
  )
  
  WHERE r.revisions &gt; 0 OR t.closed &gt; 0
  ORDER BY
     t.`author`,
     t.`month-week`
  </code></pre>
  <p>MySQL Server/Client version: 5.0.33</p>
  <p>--</p>
  <pre><code>SELECT
     t.fields,
     r.fields
  FROM
  (
     (
        SELECT    tc.fields
        FROM      tc
        WHERE     tc.field &gt; NOW() - INTERVAL 2 MONTH
        GROUP BY  tc.field
     ) t
     LEFT JOIN
     (
        SELECT    rev.fields
        FROM      rev
        WHERE     rev.field &gt; NOW() - INTERVAL 2 MONTH
        GROUP BY  rev.field
     ) r ON t.field = rev.field
  )
  UNION
  (
     (
        SELECT    tc.fields
        FROM      tc
        WHERE     tc.field &gt; NOW() - INTERVAL 2 MONTH
        GROUP BY  tc.field
     ) t
     RIGHT JOIN
     (
        SELECT    rev.fields
        FROM      rev
        WHERE     rev.field &gt; NOW() - INTERVAL 2 MONTH
        GROUP BY  rev.field
     ) r ON t.field = rev.field
  )
  WHERE t.field &gt; 0 OR r.field &gt; 0
  ORDER BY t.field
  </code></pre>
  <p>Hope it helps</p>
  <hr />
  <p>Thanks to <code>pst</code> for the help, I forgot to add the <code>SELECT * FROM</code> around the tables in the <code>UNION</code></p>
  <p>Here is the working query for anyone that wants to track the user productivity using <code>TRAC</code></p>
  <pre><code>SELECT
     `author` __group__,
     `month-week`,
     changes,
     tickets,
     closed,
     `revisions`
  FROM
  (
  (
     SELECT
        t.`author`,
        t.`month-week`,
        t.changes,
        t.tickets,
        t.closed,
        r.`revisions`
     FROM
     (
        SELECT
           CONCAT(
              MONTH(FROM_UNIXTIME(tc.`time`/1000000)),
              '-',
              WEEK(FROM_UNIXTIME(tc.`time`/1000000))
           ) `month-week`,
           COUNT(IF(tc.`field` = &quot;comment&quot;,tc.ticket,NULL)) `changes`,
           COUNT(DISTINCT tc.`ticket`) `tickets`,
           COUNT(DISTINCT IF(tc.`field` = &quot;status&quot; AND tc.`newvalue` = &quot;closed&quot;,tc.`ticket`,NULL)) `closed`,
           tc.`author`
        FROM `ticket_change` tc
        WHERE
           tc.`time` &gt; UNIX_TIMESTAMP(NOW()-INTERVAL 2 MONTH)*1000000
        GROUP BY
           tc.`author`,
           YEARWEEK(FROM_UNIXTIME(tc.`time`/1000000))
        ORDER BY NULL
     ) `t`
     LEFT JOIN
     (
        SELECT
           CONCAT(
              MONTH(FROM_UNIXTIME(r.`time`/1000000)),
              '-',
              WEEK(FROM_UNIXTIME(r.`time`/1000000))
           ) `month-week`,
           COUNT(DISTINCT r.`rev`) `revisions`,
           r.`author`
        FROM `revision` r
        WHERE
           r.`time` &gt; UNIX_TIMESTAMP(NOW()-INTERVAL 2 MONTH)*1000000
        GROUP BY
           r.`author`,
           YEARWEEK(FROM_UNIXTIME(r.`time`/1000000))
        ORDER BY NULL
     ) `r` ON t.`month-week` = r.`month-week` AND t.`author` = r.`author`
  ) 
  UNION
  (
     SELECT
        t.`author`,
        t.`month-week`,
        t.changes,
        t.tickets,
        t.closed,
        r.`revisions`
     FROM
     (
        SELECT
           CONCAT(
              MONTH(FROM_UNIXTIME(tc.`time`/1000000)),
              '-',
              WEEK(FROM_UNIXTIME(tc.`time`/1000000))
           ) `month-week`,
           COUNT(IF(tc.`field` = &quot;comment&quot;,tc.ticket,NULL)) `changes`,
           COUNT(DISTINCT tc.`ticket`) `tickets`,
           COUNT(DISTINCT IF(tc.`field` = &quot;status&quot; AND tc.`newvalue` = &quot;closed&quot;,tc.`ticket`,NULL)) `closed`,
           tc.`author`
        FROM `ticket_change` tc
        WHERE
           tc.`time`&gt; UNIX_TIMESTAMP(NOW()-INTERVAL 2 MONTH)*1000000
        GROUP BY
           tc.`author`,
           YEARWEEK(FROM_UNIXTIME(tc.`time`/1000000))
        ORDER BY NULL
     ) `t`
     RIGHT JOIN
     (
        SELECT
           CONCAT(
              MONTH(FROM_UNIXTIME(r.`time`/1000000)),
              '-',
              WEEK(FROM_UNIXTIME(r.`time`/1000000))
           ) `month-week`,
           COUNT(DISTINCT r.`rev`) `revisions`,
           r.`author`
        FROM `revision` r
        WHERE
           r.`time`&gt; UNIX_TIMESTAMP(NOW()-INTERVAL 2 MONTH)*1000000
        GROUP BY
           r.`author`,
           YEARWEEK(FROM_UNIXTIME(r.`time`/1000000))
        ORDER BY NULL
     ) `r` ON t.`month-week` = r.`month-week` AND t.`author` = r.`author`
  ) 
  ) mtb
  WHERE revisions &gt; 0 OR closed &gt; 0
  ORDER BY
     `author`,
     `month-week`
  </code></pre>