CODEWITHSUNDEEP
sql
OriyanJ
OriyanJ

Reputation: 37

Query that returns parent items with no children

I have a table that looks like this:

enter image description here

It has products. Some are related to a parent product by a "Parent ID".

I need a query that will only select parent products that don't have any children related to it. Such as "Product-4".

Can anyone help me with this? It seems quite an easy solution.

Upvotes: 2

Views: 73

Answers (3)

Menelaos
Menelaos

Reputation: 25725

Update

I didn't realize this table had a relationship with itself.

The following should work:

 SELECT *
FROM   my_table m1
WHERE  NOT exists
       (
              SELECT *
              FROM   my_table m2
              WHERE  m1.id = m2.parent_id)
AND    parent_id = 0;

This should not select children.

Upvotes: 0

Mahedi Sabuj
Mahedi Sabuj

Reputation: 2944

You can try this:-

  1. Get Parent Product by checking ParentId = 0
  2. Remove those ParentId who has children product

Here, Full Query,

Select * from Table_Name 
Where ParentId = 0
   AND Id NOT IN (Select DISTINCT ParentId from Table_Name WHERE ParentId NOT NULL)

Upvotes: 1

Almaju
Almaju

Reputation: 1383

I would say something like: SELECT * FROM parents WHERE (SELECT COUNT(*) FROM products WHERE products.parent_id = parents.id) = 0 but not sure and can't test it right now.

Upvotes: 0

Related Questions