I have a table called `connections` that shows mirrored user connections.

For example:

* Users 1 and 2 are connected
* Users 2 and 3 are connected
* Users 4 and 6 are connected
* Users 5 and 6 are connected

The resulting table looks like this:

| user\_id | connected\_to |
| --- | --- |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
| 3 | 2 |
| 4 | 6 |
| 6 | 4 |
| 5 | 6 |
| 6 | 5 |

I want to form sub-networks by joining all users that have other users in common and assign each of them the smallest `user_id`.

The result I'm looking for is:

| user\_id | subnetwork\_id |
| --- | --- |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 4 |
| 5 | 4 |
| 6 | 4 |

The logic is as follows:

1. Users 1, 2 and 3 are all connected through 2. `user_id` 1 is the smallest of all three, so `subnetwork_id` should equal 1.
2. Users 4, 5 and 6 are all connected through 6. `user_id` 4 is the smallest of all three, so `subnetwork_id` should be 4.

---

I wrote a recursive query (**will post in future edit**) that self-joins the `connections` table with itself on `user_id` and `connected_to`.

My query does not work with the second example (sub-network 4, 5 and 6) because the greatest `user_id` is the one that connects them all together.

How can I go from table `connections` to the desired result?