Question 114438 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Linking two related records in the same table, and giving them ID's (SQL)

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
Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    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
    Login or Signup to reply.
  3. 0

    I believe this will do the update:

    update observation o
set
  related_observation_id = r2.result_id
from result r1, result r2
where
  o.observation_id = r1.result_id and
  r1.linking_id = r2.linking_id and
  r2.result_id != o.observation_id and
  o.related_observation_id is null

    However some warnings…

    Is it always 1:1, so two observations per linking id? If not, this will somewhat arbitrarily assign the value. If you have a preference, what would it be?

    Alternatively, you can list all related observation ids, but you would need to change your datatype.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search