MySQL: Is it possible to JOIN the GROUP-BY'd results to two SELECTs?

Question

Hi,

I have two separate SELECT statements which are both GROUP-BY'd separately e.g.:

SELECT x, y, z FROM a GROUP BY x
SELECT x, n, o FROM b GROUP BY x

I would very much like to JOIN these two SELECTs together to combine their columns, such as:

SELECT x as x1, y, z FROM a GROUP BY x 
LEFT JOIN (
  SELECT x as x2, n, o FROM b GROUP BY x)
ON x1=x2;

Is this possible? I ask because MySQL is complaining

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 'LEFT JOIN SELECT x as x2

If this is possible, any thoughts on what's wrong with my syntax?

Thanks very much!

Answer 1

This works

select * from (
    (select 1 a,2 b,3 c) t1 left join (select null a,2 b,5 c) t2 on (t1.b=t2.b)
);

Alternatively,

select * from (
    (select 1 a,2 b,3 c) t1 left join (select null a,2 b,5 c) t2 using (b)
);

Both result in

+---+---+---+------+---+---+
| a | b | c | a    | b | c |
+---+---+---+------+---+---+
| 1 | 2 | 3 | NULL | 2 | 5 |
+---+---+---+------+---+---+
1 row in set (0.00 sec)

Answer 2

There are a few ways that you can achieve this:

1. Best: Join the tables BEFORE grouping like so:

   SELECT a.x, y, z, n, o
     FROM a INNER JOIN b ON a.x = b.x
    GROUP BY a.x, b.x;
   

2. Select from the two queries as sub-queries like so:

   SELECT *
   FROM       (SELECT x, y, z FROM a GROUP BY x) AS a
   INNER JOIN (SELECT x, n, o FROM b GROUP BY x) AS b
           ON a.x = b.x;
   

Answer 3

You could also use this query:
SELECT a.x, a.y, a.z, b.x, b.o, b.n FROM a, (SELECT x, n, o FROM b GROUP BY x) as b WHERE a.x = b.x GROUP BY a.x;