i want get_data from 3 tables(res_partner, purchase_order, sale_order) by phonenumber
i try this query(1) :
"select name from res_partner where phone = '113'
union
select s.name from sale_order s inner join res_partner u on u.id = s.partner_id where u.phone = '113'
union
select p.name from purchase_order p inner join res_partner u on u.id = p.partner_id where u.phone = '113'
;
get result
name
--------
P00006 (purchase.name)
Grab (user.name)
S00003 (sale.name)
P00007
S00005
P00003
S00002
P00008
P00004
P00009
S00004
but i cant split result to purchase.name,sale.name,user.name
when i try another query(2)
select r.name,r.email,r.phone,s.name,p.name from res_partner as r inner join sale_order as s on r.id = s.partner_id inner join purchase_order as p on r.id = p.partner_id;
i got duplicate record
name | phone | s.name | p.name
------+-------+--------+--------
Grab | 113 | S00005 | P00006
Grab | 113 | S00003 | P00006
Grab | 113 | S00004 | P00006
Grab | 113 | S00002 | P00006
Grab | 113 | S00005 | P00003
Grab | 113 | S00003 | P00003
Grab | 113 | S00004 | P00003
Grab | 113 | S00002 | P00003
Grab | 113 | S00005 | P00004
Grab | 113 | S00003 | P00004
Grab | 113 | S00004 | P00004
Grab | 113 | S00002 | P00004
Grab | 113 | S00005 | P00007
Grab | 113 | S00003 | P00007
Grab | 113 | S00004 | P00007
Grab | 113 | S00002 | P00007
Grab | 113 | S00005 | P00008
Grab | 113 | S00003 | P00008
Grab | 113 | S00004 | P00008
Grab | 113 | S00002 | P00008
Grab | 113 | S00005 | P00009
Grab | 113 | S00003 | P00009
Grab | 113 | S00004 | P00009
Grab | 113 | S00002 | P00009
can i get any solution split result to purchase.name,sale.name,user.name from query(1) or remove duplicate record from query(2)