• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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:
Base Query DS
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:
 Query With Parent Child Relationship
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.
0
fullbugg
Asked:
fullbugg
  • 3
  • 2
1 Solution
 
knightEknightCommented:
which is active -- 0 or -1 ?
0
 
fullbuggAuthor Commented:
Sorry, Active = -1

0
 
CboudrozCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
fullbuggAuthor Commented:
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.

0
 
CboudrozCommented:
you need to changes the where clause in the NOT exists

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

Open in new window

0
 
fullbuggAuthor Commented:
Thanks again, but this one doesn't disable anything.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now