I have a table of subscription users, with a contact ID and order ID. Multiple contacts can be linked to one order and a contact can be linked to multiple orders. I'm trying to take a given order, look at the users for that order, identify any other orders any of those users are associated with, and link them as one company as the table shows:
Apparently I was wrong and SQL does provide a way to solve your problem. Here is my solution. It is not optimized regarding the runtime efficiency - if that's necessary, I could take another look at it:
with recursive
incompany(contact, order1, order2)
as (select contact, o1.orderID as order1, o2.orderID as order2
from orders o1 join orders o2 using (contact)
union
select o1.contact, inc.order1, o2.orderID as order2
from incompany as inc, orders as o1, orders as o2
where inc.order2=o1.orderID and o1.contact=o2.contact)
select contact, sum(order1) as MyNewCompanyID from
(select distinct contact, order1 from incompany) as foo
group by contact;
In the first part, I am defining a recursive query incompany, that does most of the work and assigns every orderID that is used by another contact in the same company to that contact. So select * from incompany; in itself would return the following table:
+---------+--------+--------+
| contact | order1 | order2 |
+---------+--------+--------+
| a | 1 | 1 |
| a | 1 | 2 |
| a | 2 | 1 |
| a | 2 | 2 |
| a | 3 | 1 |
| a | 3 | 2 |
| b | 1 | 1 |
| b | 2 | 1 |
| b | 3 | 1 |
| c | 1 | 1 |
| c | 2 | 1 |
| c | 3 | 1 |
| d | 1 | 2 |
| d | 1 | 3 |
| d | 2 | 2 |
| d | 2 | 3 |
| d | 3 | 2 |
| d | 3 | 3 |
| e | 4 | 4 |
| e | 4 | 5 |
| e | 5 | 4 |
| e | 5 | 5 |
| f | 4 | 4 |
| f | 5 | 4 |
| g | 4 | 5 |
| g | 5 | 5 |
+---------+--------+--------+
The second part of the query basically just shortens this table to the necessary minimum and then creates a new kind of "company ID" (MyNewCompanyID) as the sum of all the orders, this company uses. With your example, it returns the following table:
+---------+----------------+
| contact | MyNewCompanyID |
+---------+----------------+
| a | 6 |
| b | 6 |
| c | 6 |
| d | 6 |
| e | 9 |
| f | 9 |
| g | 9 |
+---------+----------------+
with part doesIn the first part, I am defining something like a temporary view, that I can later access like a regular table. Inside, it has to consist of a regular query first, unioned by a second query, that is allowed to recursively access itself.
If you want to know more about this kind of recursion, I recommend these two videos:
To assign a unique number each company, you should probably rather use row_number() as explained here: https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-row_number-function/