stored procedures - MySQL - reuse nested select statement -

i'm wondering whether there's more efficient way of writing following query (note reuse of same select statement resulting in a). wouldn't worry it, need add 1 or 2 more 'parent' levels this, , seems there must better way naive approach.

select a.*, i.invoice_status ( select e.*, il.invoice_line_id, il.invoice_line_invoice_id        expense e        left join invoice_line il        on e.expense_id = il.invoice_line_expense_id        union        select e.*, il.invoice_line_id, il.invoice_line_invoice_id        expense e        right join invoice_line il        on e.expense_id = il.invoice_line_expense_id) left join invoice on a.invoice_line_invoice_id = i.invoice_id union select a.*, i.invoice_status, i.invoice_due_date ( select e.*, il.invoice_line_id, il.invoice_line_invoice_id        expense e        left join invoice_line il        on e.expense_id = il.invoice_line_expense_id        union        select e.*, il.invoice_line_id, il.invoice_line_invoice_id        expense e        right join invoice_line il        on e.expense_id = il.invoice_line_expense_id) right join invoice on a.invoice_line_invoice_id = i.invoice_id; 

thanks!

in oracle, create called common table expression with keyword. works follows:

with oracle_cte (select * dual connect level <= 10); 

in mysql, don't have such luxuries in oracle. so, follows:

create procedure create temporary table stores result set of following:

select e.*, il.invoice_line_id, il.invoice_line_invoice_id expense e left join invoice_line il on e.expense_id = il.invoice_line_expense_id union select e.*, il.invoice_line_id, il.invoice_line_invoice_id expense e right join invoice_line il on e.expense_id = il.invoice_line_expense_id; 

and then, reuse temporary table in both of queries across applying union.

select a.*, i.invoice_status cte_mysql_table left join invoice on a.invoice_line_invoice_id = i.invoice_id union select a.*, i.invoice_status, i.invoice_due_date cte_mysql_table right join invoice on a.invoice_line_invoice_id = i.invoice_id;