Identify database records that point to each other

Let’s say I have a database of user records and each time the user takes an action, a record is stored. Each action has a “parent” action in the database, and has a column that points to the id of that parent action. This creates a chain of all the user’s actions:

id  |  action  |  parent_id
----+----------+------------
1   |  create  |
2   |  update  |  1
3   |  update  |  2

Let’s say through some mistake, two records ended up pointing at each other, where the parent_id of each is the id of the other:

id  |  action  |  parent_id
----+----------+------------
1   |  create  |
2   |  update  |  3
3   |  update  |  2

How can I construct a SQL command to identify rows that point to one another (ids 2 and 3)? I don’t even know where to start. Thank you in advance

Answer

Here’s one option using exists:

select * 
from yourtable t1
where exists (
  select 1
  from yourtable t2
  where t1.id = t2.parent_id and t2.id = t1.parent_id)

Advertisement

Answer