T-SQL How to exclude a child record when it's also it's own parent record?-CodePudding

I have a scenario like the following:

create table #Example (
    id int
    , overall_id int
    , parent_id int
    , child_id int
    );

insert into #Example values
    (1, 25963, 491575090, 491575090)
    ,(2, 25963, 547952026, 491575090)
    ,(3, 25963, 547952026, 230085039)
    ,(4, 25963, 547952026, 547952026);


select e.*
from #Example as e;


drop table #Example;

I want to exclude the record with id "2" because that is it's own parent record (see id "1").

I do not want to exclude 3, because the child record is not it's own parent record. And I don't want to exclude 1 and 4 because those are their own parent records.

One problem is that in my business scenario, I have no corresponding "ID" field, that is something I provided in this example so that I could refer to each row uniquely.

Any help on techniques to exclude record 2 would be greatly appreciated!

CodePudding user response：

I still don't understand the question, but the expected result falls out of:

select *
  from #Example as E
  where not exists (
    select 42
      from #Example as IE
      where
        -- There is a row that is self parenting?!
        IE.parent_id = IE.child_id and
        -- The row under consideration is related in a child/parent way?
        IE.child_id = E.child_id and
        -- It isn't the same row as we're considering.
        IE.id <> E.id );

See dbfiddle.