CODEWITHSUNDEEP
sql
uwe
uwe

Reputation: 4077

SQL query to find parents with no child

I have a products table and a categories table. The categories have a parent-child hierarchy with only 2 levels (parent and child). Products can be associated with many categories via table PC.

How can I find all products that have a parent category but no child from that same parent?

Upvotes: 0

Views: 408

Answers (3)

Black Light
Black Light

Reputation: 2404

select parent.name
from parent as p
join holidays as h on (h.person_id = p.person_id)
where (h.date not in (select date from school_holidays))

:o)

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

Could look like this:

SELECT p.prod_id, p.prod
FROM   products p
JOIN   pc pcp ON pcp.prod_id = p.prod_id
JOIN   category cp ON cp.cat_id = pcp.cat_id
WHERE EXISTS (
    SELECT *
    FROM   category c0
    WHERE  c0.parent_id = cp.cat_id
    )
AND NOT EXISTS (
    SELECT *
    FROM   pc pcc
    JOIN   category cc ON cc.cat_id = pcc.cat_id
                      AND cc.parent_id = cp.cat_id
    WHERE  pcc.prod_id = p.prod_id
    )
GROUP BY p.prod_id, p.prod

"Find all products with a category that has a child (= parent category) where no child of this category is linked to the same product."
This assumes that the parent-child relation is implemented with a single column in the table category:

parent_id REFERENCES category (cat_id)

If you were a nicer guy, you would have specified all that in the question.

Upvotes: 1

TheOx
TheOx

Reputation: 2228

Assuming:

  1. Your categories table has a field named "parent_category" in it that contains the ID of the parent category for a child category and 0 if it's a parent category.
  2. Your PC table contains a productid and categoryid field.

then

SELECT * FROM products 
LEFT JOIN pc ON products.productid = pc.productid 
LEFT JOIN categories ON categories.categoryid = pc.categoryid 
WHERE categories.parent_category_id = 0

Upvotes: 0

Related Questions