Imagine I have the following tables:
| Numbers PK |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| Numbers FK 1 | Numbers FK 2 |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 8 | 8 |
| 10 | 4 |
| 7 | 3 |
| 4 | 9 |
| 1 | 6 |
| 3 | 9 |
| 4 | 6 |
| 5 | 6 |
I have the following tables: "Numbers PK" as Primary key and another 2 tables that are related one with each other that are foreign keys of Numbers.
I am trying to make a query to select the number(s) from the table "NumbersFK2" that are related with all the numbers of "Numbers PK".
As you can see in this example the solution would be 1 as 1 is related with 1-10 in the tables "Numbers FK1" and "Numbers FK2"
I have tried to solve and after some days I need some help as I don't know how could I do it. I appreciate the help. Thanks
CodePudding user response：
We use dense_rank() to count the Numbers_PK in case they're not consecutive. Then we left join, group by and count(distinct Numbers_PK).
with t3 as (
select Numbers_PK
,dense_rank() over(order by Numbers_PK) as dns_rnk
from t
)
select Numbers_FK_2
from t3 left join t2 on t2.Numbers_FK_1 = t3.Numbers_PK
group by Numbers_FK_2
having count(distinct Numbers_PK) = max(dns_rnk)
| Numbers_FK_2 |
|---|
| 1 |