Avoid using JOIN with OR

Question

I have two tables like below:

table_1

id_1	id_2	num_purchases
1	a	20
1	a	100
2	b	21
3	c	22
4	d	23
5	e	24

table_2

id	order	colour
1	apple	red
a	apple	red
b	apple	red
3	apple	red
c	banana	yellow
d	banana	yellow
5	banana	yellow

Curretly used code looks like below:

select
t1.id_1,
sum(t1.num_purchases) as sum_purchases,
array_agg(t2.order) as orders

from table_1 t1

left join (select id, order from table_2) t2
on t2.id = t1.id_1

group by t1.id_1

However, as id in table_2 comes in either id_1 or id_2, when id from table_2 comes in id_2, the rows cannot be matched. In order to correctly join the tables so that it considers both id_1 and id_2, how should the code be structured? I'd like to avoid using JOIN with OR.

Desired outcome should look like below:

id	sum_purchases	num_purchases
1	120	apple,apple
2	21	apple
3	22	apple,banana
4	23	banana
5	23	banana

Answer 1

Try this

with cte1 as (
select id_1,id_2,num_purchases,b.id idb,b."order" orderB,b.colour colourB,c.id idc
  ,c."order" orderC,c.colour colourC
from table_1 a
left join table_2 b on b.id=a.id_1
left join table_2 c on c.id=a.id_2
)
,cte2 as(
select  id_1,id_2,num_purchases
  ,case when orderB is null then orderC
        when orderC is null then orderB
        when orderC=orderB  then orderB
   else concat(orderB,',',orderC) 
   end ord
from cte1
)
select id_1, sum(num_purchases) as sum_purchases
   ,array_agg(ord) as orders
from cte2
group by id_1
order by id_1;

id_1	sum_purchases	orders
1	120	apple,apple
2	21	apple
3	22	apple,banana
4	23	banana
5	24	banana

Example