Can't make an UNION of a LEFT and RIGHT JOIN work. (trying to emulate an outer join)

`At the bottom of the post you can find a simplify query of what I am trying to do`

I am trying to create a query that shows the productivity for all the users in our TRAC 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 RIGHT JOIN and an UNION to emulate the OUTER JOIN that MySQL doesn't support, and not I am getting this error:

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

If I comment out one of the two parts of the UNION it works.

I already tried to give different aliases to the tables

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` = "comment",tc.ticket,NULL)) `changes`,
         COUNT(DISTINCT tc.`ticket`) `tickets`,
         COUNT(DISTINCT IF(tc.`field` = "status" AND tc.`newvalue` = "closed",tc.`ticket`,NULL)) `closed`,
         tc.`author`
      FROM `ticket_change` tc
      WHERE
         tc.`time` > 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` > 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` = "comment",tc.ticket,NULL)) `changes`,
         COUNT(DISTINCT tc.`ticket`) `tickets`,
         COUNT(DISTINCT IF(tc.`field` = "status" AND tc.`newvalue` = "closed",tc.`ticket`,NULL)) `closed`,
         tc.`author`
      FROM `ticket_change` tc
      WHERE
         tc.`time` > 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` > 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 > 0 OR t.closed > 0
ORDER BY
   t.`author`,
   t.`month-week`

MySQL Server/Client version: 5.0.33

SELECT
   t.fields,
   r.fields
FROM
(
   (
      SELECT    tc.fields
      FROM      tc
      WHERE     tc.field > NOW() - INTERVAL 2 MONTH
      GROUP BY  tc.field
   ) t
   LEFT JOIN
   (
      SELECT    rev.fields
      FROM      rev
      WHERE     rev.field > NOW() - INTERVAL 2 MONTH
      GROUP BY  rev.field
   ) r ON t.field = rev.field
)
UNION
(
   (
      SELECT    tc.fields
      FROM      tc
      WHERE     tc.field > NOW() - INTERVAL 2 MONTH
      GROUP BY  tc.field
   ) t
   RIGHT JOIN
   (
      SELECT    rev.fields
      FROM      rev
      WHERE     rev.field > NOW() - INTERVAL 2 MONTH
      GROUP BY  rev.field
   ) r ON t.field = rev.field
)
WHERE t.field > 0 OR r.field > 0
ORDER BY t.field

Hope it helps

Thanks to pst for the help, I forgot to add the SELECT * FROM around the tables in the UNION

Here is the working query for anyone that wants to track the user productivity using TRAC

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` = "comment",tc.ticket,NULL)) `changes`,
         COUNT(DISTINCT tc.`ticket`) `tickets`,
         COUNT(DISTINCT IF(tc.`field` = "status" AND tc.`newvalue` = "closed",tc.`ticket`,NULL)) `closed`,
         tc.`author`
      FROM `ticket_change` tc
      WHERE
         tc.`time` > 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` > 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` = "comment",tc.ticket,NULL)) `changes`,
         COUNT(DISTINCT tc.`ticket`) `tickets`,
         COUNT(DISTINCT IF(tc.`field` = "status" AND tc.`newvalue` = "closed",tc.`ticket`,NULL)) `closed`,
         tc.`author`
      FROM `ticket_change` tc
      WHERE
         tc.`time`> 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`> 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 > 0 OR closed > 0
ORDER BY
   `author`,
   `month-week`

Solution

Answers is in the question itself, but here it is in case someone else needs it:

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` = "comment",tc.ticket,NULL)) `changes`,
         COUNT(DISTINCT tc.`ticket`) `tickets`,
         COUNT(DISTINCT IF(tc.`field` = "status" AND tc.`newvalue` = "closed",tc.`ticket`,NULL)) `closed`,
         tc.`author`
      FROM `ticket_change` tc
      WHERE
         tc.`time` > 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` > 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` = "comment",tc.ticket,NULL)) `changes`,
         COUNT(DISTINCT tc.`ticket`) `tickets`,
         COUNT(DISTINCT IF(tc.`field` = "status" AND tc.`newvalue` = "closed",tc.`ticket`,NULL)) `closed`,
         tc.`author`
      FROM `ticket_change` tc
      WHERE
         tc.`time`> 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`> 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 > 0 OR closed > 0
ORDER BY
   `author`,
   `month-week`