How to make output from one mysql table into two columns?

Question

MySQL

There is a table (cash desk) containing movements on the account, in one of the columns in the form of a number, the operation is related to expenses or receipts.
Task: in one query, get a selection in order to see the income column and the expense column as a result.
The request turned out like this:

QR.SQL.Add('select t1.*,t2.* from (select  @i := @i + 1 AS `RowNumber`, kp.id as id,date(payment_date) as payment_date, pt.payment_name as oper_name, payment_text as oper_text, payment_amount as oper_sum, if (payment_type=1,"Приход","Расход") as operation '+
             ' from (SELECT @i:=0) AS `RowNumberTable`, kassa_payments kp left join kassa_payment_types pt on kp.payment_type_id=pt.id where payment_filial='+inttostr(selected_filial)+' and payment_type=1 and  date(payment_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'"   order by payment_type, payment_date) as t1 '+
             'left outer join'+
             '(select  @j := @j + 1 AS `RowNumber`, kp.id as id2,date(payment_date) as payment_date2, pt.payment_name as oper_name2, payment_text as oper_text2, payment_amount as oper_sum2, if (payment_type=1,"Приход","Расход") as operation2'+
             ' from (SELECT @j:=0) AS `RowNumberTable`, kassa_payments kp left join kassa_payment_types pt on kp.payment_type_id=pt.id where payment_filial='+inttostr(selected_filial)+' and payment_type=-1 and date(payment_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'"  order by payment_type, payment_date) as  t2 '+
             ' on t1.Rownumber=t2.Rownumber');

With the help of him, we managed to solve the problem, but with one caveat that there should be more records in the left semi-table than in the right one, otherwise the right part is cut off by the number of records in the left one.
is it possible to overcome this somehow?

Answer 1

while I came up with a crutch in the form of a previous query that counts the number of elements in the left and right tuples, and if there are more in the right, then swap the subqueries, putting the thicker one in the left (first) place