I have the following query from ODOO using Postgres to get the order_id and the related product_id and partner_id :

select SOL.order_id ,SOL.product_id,SO.partner_id
from sale_order AS SO
left join sale_order_line AS SOL on SO.id=SOL.order_id
left join res_partner AS RP on RP.id=SO.partner_id
where SO.id=2

Result :

order_id product_id partner_id
2        1          12
2        3          12

I want to get the type for the account used like below :

select SOL.order_id ,SOL.product_id,SO.partner_id
,av.journal_id,aj.type

from sale_order AS SO
left join sale_order_line AS SOL on SO.id=SOL.order_id
left join res_partner AS RP on RP.id=SO.partner_id
left join Account_Voucher AS AV on AV.partner_id=SO.partner_id
left join Account_Journal AS AJ on AJ.id=AV.journal_id
where SO.id=2

I get the following result :

order_id    product_id  partner_id  journal_id  type
2           1           12          13          bank
2           1           12          14          cash
2           1           12          17          cash
2           1           12          18          bank
2           3           12          13          bank
2           3           12          14          cash
2           3           12          17          cash
2           3           12          18          bank

If we check the different journal_id for the partner_id=12

select * from Account_Voucher where partner_id=12

id  partner_id  journal_id
84  12          13  
90  12          14  
57  12          17  
24  12          18

The missing part here is for the first query we cannot find for each sale_order and sale_order_line which account_voucher/account_journal is used. As you can check that the result of the second query is based on multiplying the result of the first one by the third one. We are looking for a table in Odoo that links sale_order and sale_order_line and account_voucher/account_journal used for that specific transaction.