0

I have the following result sets:

Those values come from a relational table of

ProductId, GroupId
1 | 4
2 | 4
2 | 5
3 | 4
3 | 5

CategoryId | ProductId
1 | 1
1 | 2
1 | 3

All the following "Id" are from the category of those produtcts

Example 1:       Example 2:           Example 3:

|Id |Group|      |Id |Group    |      |Id |Group    |
-----------      ---------------      ---------------
| 1 | 4   |      | 1 | 4,5     |      | 1 | 3,5     |
| 1 | 4,5 |      | 1 | 3,4,5,6 |      | 1 | 3,4,5,6 |
| 1 | 5,7 |      | 1 | 5,7     |      | 1 | 4,5     |
-----------      ---------------      ---------------

I need to process those tables to get the following results

Result 1:        Result 2:            Result 3:

|Id |Group|      |Id |Group    |      |Id |Group    |
-----------      ---------------      ---------------
| 1 | 4,5 |      | 1 | 3,4,5,6 |      | 1 | 3,4,5,6 |
| 1 | 4,5 |      | 1 | 3,4,5,6 |      | 1 | 3,4,5,6 |
| 1 | 5,7 |      | 1 | 5,7     |      | 1 | 3,4,5,6 |
-----------      ---------------      ---------------

Explanation for that, those columns indicate where the price of some item should be placed, and all related prices should be in the same table if possible, so when a group can be joined with other it should result in empty spaces for the columns that weren't originally for that product so:

Using example 1 this is the final result:

         | G4 | G5 |
--------------------
Product1 | 10 |    |
Product2 |    | 15 |
Product3 | 14 | 18 |
--------------------

         | G5 | G7 |
--------------------
Product1 | 10 | 25 |
Product2 |    | 15 |
--------------------

Using the example 3 this is the final result:

         | G3 | G4 | G5 | G6 |
------------------------------
Product1 | 10 |    | 15 | 20 |
Product2 |    |    | 17 |    |
Product3 | 14 | 18 |    |    |
------------------------------

But I'm completly clueless on how to do those group joins (the empty spaces in the result set is not a problem.

LoadIt
  • 137
  • 1
  • 1
  • 9
  • Everything has an id of 1. And there isn't an example 4. I've studied this for a good ten minutes and can't make sense of very much of it. – shawnt00 May 26 '20 at 21:46
  • @shawnt00 those Ids = 1 are the category of those products, those groups are dependent on the categoryId, but they come from the products – LoadIt May 27 '20 at 08:12

1 Answers1

0

I don't fully understand the problem yet but this might help you determine which sets of groups are subsets of larger ones. As I best I can tell that's the transitive relationship you indicated in the title.

select t1.id as id1, t2.id as id2
from T t1 full outer join T t2 on t2.grp = t1.grp
group by t1.id, t2.id
having count(distinct t1.grp) < count(distinct t2.grp) and count(t1.id) = count(*)

Now that you have an adjacency list or a hierarchy you could try some approaches as in this question to find all the "maximal" or top-level sets: Finding a Top Level Parent in SQL

If you have a limit on the number of possible groups then Gordon's answer there may be sufficient without all the recursive complications.

shawnt00
  • 16,443
  • 3
  • 17
  • 22
  • The problem is it's not really a hierarchy I just have products that have multiple PriceCategory. All those products also have a ProductCategory. For each ProductCategory I need to group the items based on their PriceCategories. and that's what generates those tables I gave as example where the Group = EachPriceCategory that each product has a price for. After that I need to merge sets that overlap like in the samples, – LoadIt May 27 '20 at 18:51
  • @LoadIt I deduced that you are looking for some kind of logic to locate subset within the "groups". Everything I wrote above was directed to my understanding of your need to "merge" the "overlap". My apologies, the rest of your intentions are still confusing to me. – shawnt00 May 27 '20 at 21:40