How to add a JOIN to a UNION of two SELECTs of the same table?

Question

MySQL

There is a table units with columns id, user, group, amount, multi_id, x, y.
There is a multi table with columns id, value.
There is a query to fetch records from units:

(SELECT id, amount FROM units WHERE group_id = 0 AND x=? AND y=?) UNION (SELECT MIN(id), SUM(amount) as amount FROM units WHERE group!= 0 AND x=? AND y=? GROUP BY group) LIMIT ?,?

Union due to the fact that it is necessary to group only units that are in a group (group> 0), if some record has group = 0, then it does not need to be grouped with anything. The question is how to do a JOIN with this query, multiply units.amount by multi.value where units.multi_id comes off multi.id and return as total_power. Can you please tell me how to make a good (fast with a large number of records) query?

Answer 1

SELECT
t.id, t.amount, t.amount*multi.value AS total_power
FROM (
SELECT id, amount, multi_id FROM units WHERE group_id = 0 AND x=? AND y=?
UNION (
SELECT g.id, g.amount , units.multi_id
FROM (
SELECT MIN(id) AS id, SUM(amount) as amount FROM units WHERE group!= 0 AND x=? AND y=? GROUP BY group
) g, units
WHERE
g.id = units.id
) t
LEFT JOIN
multi
ON
t.multi_id = multi.id;

Answer 2

select * from ( select ... union select) as t1
join t2 on t1....