asked on

Disable arent when all children are disabled

Hello Experts,
I have an issue.

I have a single table that houses a parent/child data relationship:

When I run this query:
SELECT            DISTINCT(a.idCategory),
                  a.categorydesc,
                  a.idParentCategory,
                  a.active,
                  b.idCategory,
                  b.categorydesc,
                  b.idParentCategory,
                  b.active
FROM categories a
      JOIN categories b ON a.idcategory = b.idparentcategory
WHERE NOT ((a.idcategory = 1 AND a.idparentcategory = 1) AND (b.idcategory = 1 AND b.idparentcategory = 1))

I get these results:

What I am trying to do is write a single query that will disable the parent category only when all child categories are disabled.

Please help... I am stuck.

knightEknight

which is active -- 0 or -1 ?

fullbugg

ASKER

Sorry, Active = -1

Cboudroz

This will update all the Parent whiteout active child.

So if no child or no active child it will be disable the parent, if one active child nothing will be done.

UPDATE A
SET Active = 0
FROM categories a 
WHERE 
	NOT EXISTS
	(
		select 
			*
		from 
			categories b 
		where 
			a.idcategory = b.idparentcategory		
			and b.Active =-1
	)

Open in new window

fullbugg

ASKER

Thanks for the reply... however this query shut off all child categories...

I just thought of something... The Root is the Parent category. Therefore what would have to be disabled would be the grand children.

ASKER CERTIFIED SOLUTION

Cboudroz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fullbugg

ASKER

Thanks again, but this one doesn't disable anything.