Table "Observation" is filled with records, that come from table "Result" (observation_id = result_id)
In table "Observation" every record has a corresponding record that is also in the same table. Is there a way to link those two records, giving them their associated id. Tried a number of things but without any luck. Anyone care to take a look?
Current "Observation" table:
| observation_id pk | related_observation_id |
|---|---|
| 1 | |
| 8451 | |
| 2 | |
| 8452 | |
| 3 | |
| 8453 |
"Result" table:
| result_id pk | value | linking_id |
|---|---|---|
| 1 | 234 | 1 |
| 8745 | 231 | 1 |
| 2 | 653 | 2 |
| 8746 | 318 | 2 |
| 3 | 774 | 3 |
| 8747 | 321 | 3 |
Desired "Observation" table:
| observation_id pk | related_observation_id |
|---|---|
| 1 | 8745 |
| 8745 | 1 |
| 2 | 8746 |
| 8746 | 2 |
| 3 | 8747 |
| 8747 | 3 |
CodePudding user response：
using self join and condition like this:
select
r1.result_id as observation_id
, r2.result_id as related_observation_id
from result_table r1
left join result_table r2
on (r1.result_id = r2.linking_id or r2.result_id = r1.linking_id)
and r1.result_id <> r2.result_id
CodePudding user response：
SELECT
r1.result_id AS observation_id
, r2.result_id AS related_observation_id
FROM result_table r1
LEFT JOIN result_table r2
on r1.result_id = r2.linking_id
WHERE observation_id <> related_observation_id