OracleSql Join several column

Question

I have a service table:

id	service
11	ser_1
22	ser_2
33	ser_3
44	ser_4
55	ser_5
66	ser_6
77	ser_7
88	ser_8

also, A combination of these services creates a new service and a table like this:

offer_1	offer_2	offer_3	combination	price
11	22	33	comb_1	300
11	33		comb_2	200
	44	22	comb_3	100
44		11	comb_4	100
55			comb_5	150
11	22	44	comb_6	200
		77	comb_7	400
	22	11	comb_8	150
	88		comb_9	250

It is also possible that any of the services were not in the new service table, for example: id = 66

There is a user table:

u_id	name	service_id
12	John	11
12	John	22
12	John	33
12	John	77
14	Kate	44
14	Kate	11
15	George	11
15	George	33
16	Bob	55
17	Alice	44
17	Alice	22
18	Ann	77
18	Ann	88
18	Ann	22
19	Fred	66

I want to get such a table:

u_id	name	combination
12	John	comb_1
12	John	comb_7
14	Kate	comb_4
15	George	comb_2
16	Bob	comb_5
17	Alice	comb_3
18	Ann	comb_7
18	Ann	comb_9

I tried but it does not work:

select ser.u_id,ser.name,c.combination,c.price
from
(select u.u_id,u.name,max(c1.offer_1) of1,max(c2.offer_2) of2,max(c3.offer_3) of3
  from user u
left join combination c1  on u.service_id = c1.offer_1
left join combination c2 on u.service_id = c2.offer_2 
left join combination c2 on u.service_id = c3.offer_3
  where u.service_id = (11,22,33,44,55,77,88)
group by u.u_id,u.name) ser
join combination c on ser.of1 = c.offer_1 and ser.of2 = c.offer_2 and ser.of3 = c.offer_3

Answer 1

1 method to handle such a situation is to use LISTAGG before joining them -

SELECT U.u_id, U.name, O.combination
  FROM (SELECT u_id, name, LISTAGG(service_id, ',') WITHIN GROUP (ORDER BY service_id) service_list
          FROM users
         GROUP BY u_id, name) U
  JOIN (SELECT combination, LISTAGG(offer, ',') WITHIN GROUP (ORDER BY offer) offer_list
          FROM (SELECT offer_1 offer, combination
                  FROM offer_table
                 UNION ALL
                SELECT offer_2, combination
                  FROM offer_table
                 UNION ALL
                SELECT offer_3, combination
                  FROM offer_table)
         GROUP BY combination) O ON O.offer_list = U.service_list

Demo.