I want to query a single orders table using a Postgres SQL editor (DBeaver)

| order_id | subs_id |
| -------- | --------|
| 1        | aa      |
| 2        | aa      |
| 3        | aa      |
| 4        | bb      |
| 5        | bb      |
| 6        | bb      |
| 7        | aa      |
| 8        | bb      |

All I want to do is find all orders for a subscriptions by using one of the order numbers. So if I have an order id, I want to find the other related orders for that subscription. Should be a simple process.

1. Find associated subs_id for supplied order_id
2. Find all orders for that subs_id

Here is what I tried.

select *
from orders o 
where o.subs_id in (
    select o2.subs_id
    from orders o2
    where o2.order_id = '3')

This is the desired result

| order_id | subs_id |
| -------- | --------|
| 1        | aa      |
| 2        | aa      |
| 3        | aa      |
| 7        | aa      |

Thanks!

CodePudding user response：

You can join the table with itself by subs_id. For example:

select b.*
from t a
join t b on b.subs_id = a.subs_id
where a.order_id = '3'