Use one JOIN more times as there are more columns

To achieve your requirement, you must create a concept of order in the groups associated with a given product. This is because you end up choosing up to five groups (and areas) in a specific order (for example, group 1, group 2, group 3, etc.) for a given product.

Ordering for the entire set of results can be achieved with a proposal order by

, but this is not enough because we need ordering for each product independently. This is why section functions exist; allow work in subgroups of the result set without combining the entire group into one record in the process (which will do group by

).

So what we can do is combine each product record with all of its groups (which, as you pointed out, can be no more than five per product), and then run the partition function row_number()

to split by product. This will allow us to create a new column that will denote each group of each product with a separate number in that set of product groups. We'll then be able to left join

five times with the tagged intermediate table, filtering for tags 1, 2, 3, 4, and 5 respectively, thus getting each group in its own connection. Then simply traversing the table links through the group table and then the scopes table to get all the information we need to create the desired result set.

create table FArea (area_id int primary key);
create table FGroup (group_id int primary key, area_id int references FArea(area_id));
create table FProduct (product_id int primary key);
create table Fgroup_product_junction (group_id int references FGroup(group_id), product_id int references FProduct(product_id));

-- product 1
insert into FProduct (product_id) values (1);
insert into FArea (area_id) values (1), (2), (3), (4), (5);
insert into FGroup (group_id,area_id) values (1,1), (2,2), (3,3), (4,4), (5,5);
insert into Fgroup_product_junction (group_id,product_id) values (1,1), (2,1), (3,1), (4,1), (5,1);

-- product 2
insert into FProduct (product_id) values (2);
insert into FArea (area_id) values (6), (7), (8);
insert into FGroup (group_id,area_id) values (6,6), (7,7), (8,8);
insert into Fgroup_product_junction (group_id,product_id) values (6,2), (7,2), (8,2);

select * from FArea;
select * from FGroup;
select * from FProduct;
select * from Fgroup_product_junction;

with t_pg as (
    select
        p.product_id,
        j.group_id,
        row_number() over (partition by p.product_id order by j.group_id) num
    from
        FProduct p
        inner join Fgroup_product_junction j on j.product_id=p.product_id
)
select
    p.product_id "Product",
    g1.group_id "Group 1",
    g2.group_id "Group 2",
    g3.group_id "Group 3",
    g4.group_id "Group 4",
    g5.group_id "Group 5",
    a1.area_id "Area 1",
    a2.area_id "Area 2",
    a3.area_id "Area 3",
    a4.area_id "Area 4",
    a5.area_id "Area 5"
from
    FProduct p
    left join t_pg j1 on j1.product_id=p.product_id and j1.num=1 left join FGroup g1 on g1.group_id=j1.group_id left join FArea a1 on a1.area_id=g1.area_id
    left join t_pg j2 on j2.product_id=p.product_id and j2.num=2 left join FGroup g2 on g2.group_id=j2.group_id left join FArea a2 on a2.area_id=g2.area_id
    left join t_pg j3 on j3.product_id=p.product_id and j3.num=3 left join FGroup g3 on g3.group_id=j3.group_id left join FArea a3 on a3.area_id=g3.area_id
    left join t_pg j4 on j4.product_id=p.product_id and j4.num=4 left join FGroup g4 on g4.group_id=j4.group_id left join FArea a4 on a4.area_id=g4.area_id
    left join t_pg j5 on j5.product_id=p.product_id and j5.num=5 left join FGroup g5 on g5.group_id=j5.group_id left join FArea a5 on a5.area_id=g5.area_id
;