Need SQL Query to Find Parent Records Without Child Records

Need SQL Query to find Parent records without child records

You can use a NOT EXISTS clause for this

SELECT ParentTable.ParentID
FROM ParentTable
WHERE NOT EXISTS (
    SELECT 1 FROM ChildTable
    WHERE ChildTable.ParentID = ParentTable.ParentID
)

There's also the old left join and check for null approach

SELECT ParentTable.ParentID
FROM ParentTable
LEFT JOIN ChildTable
  ON ParentTable.ParentID = ChildTable.ParentID
WHERE ChildTable.ChildID IS NULL

Try both and see which one works better for you.

SQL Query to find Parent records without child records.Both child and parent records are on the same table

A SELF JOIN on the table as follows would give the parents without children.

-- relations (record_id, parent_id)
SELECT parents.record_id 
FROM 
    relations parents
    LEFT JOIN relations children
    ON parents.record_id = children.parent_id
WHERE 
    children.record_id IS NULL

How can I find parent records without children?

Try this :

List<Accounting> Items = await _context.Accounting.Where(q => q.Accounting.Where(r => r.ParentAccountId == q.AccountId).count()==0).ToListAsync();

How to query all parent with no child, and last existing child of a parent when only 2 levels?

You have two requirements:

1. Get all rows not in a relationship
2. Get the last child of each parent

Essentially you're trying to get the last leaf in every relationship.

To get all rows not in a relationship, get those rows who have a null parent id and whose id is not used as a parent id on another row.

To get the last children, use a sub query get the max id for each parent id and use those ids to select the rows you need.

You could split those two requirements into two queries with a union, but since the not a parent requirement won't hurt the leaves, you can just split the null parent id or has max id requirement with an or.

SELECT *
FROM @Avis a
WHERE a.Id NOT IN (SELECT ParentId FROM @Avis WHERE ParentId IS NOT NULL)
    AND (
        a.ParentId IS NULL 
        OR a.Id IN (SELECT MAX(Id) FROM @Avis WHERE ParentId IS NOT NULL GROUP BY ParentId)
    )

Results

Id          Description  ParentId
----------- ------------ -----------
3           cccc         NULL
4           dddd         NULL
5           eeee         NULL
6           ffff         NULL
7           gggg         NULL
9           aaaa-3       1
11          bbbb-3       2

Bonus: this query will get the last leaf for each parent even if the relationships are multiple levels deep.

Rails/SQL: Find parents that have no child or have all children with a condition

Since you're using Postgres, you can use a NOT EXISTS query:

# Parents with no children
Parent.where.not('exists (?)', Child.where('children.parent_id = parents.id').select(1))

This query performs better than anything requiring a join, as an EXPLAIN will show you that Postgres will accomplish this with a Nested Loop Anti Join operation.

What is the query to get parent records against specific child or get child records against parent?

If I understand your question correctly that you don't want to insert null values in Parent_ID column then you should replace NULL with 0 and your updated code will be like:

;WITH DATA AS (
                SELECT p.PERSON_ID,p.Name, p.PARENT_ID
                FROM hierarchy p
                WHERE p.PERSON_ID = 9
                UNION ALL
                SELECT c.PERSON_ID,c.Name, c.PARENT_ID
                FROM hierarchy c
                JOIN DATA h 
                ON c.PERSON_ID = h.PARENT_ID 
          )
select * from DATA;

How to get all Parent records which doesn't have child record

You can try this LINQ,

MessageList.Where(m => !SentMessageList.Select(sm => sm.MsgId).Contains(m.MsgId));

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

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.

---

Related Topics