Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Disable arent when all children are disabled

Posted on 2011-05-12
6
Medium Priority
?
174 Views
Last Modified: 2012-06-27
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
Comment
Question by:fullbugg
  • 3
  • 2
6 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35747146
which is active -- 0 or -1 ?
0
 

Author Comment

by:fullbugg
ID: 35747169
Sorry, Active = -1

0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 35748712
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:fullbugg
ID: 35754792
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
 
LVL 7

Accepted Solution

by:
Cboudroz earned 2000 total points
ID: 35754870
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
 

Author Comment

by:fullbugg
ID: 35755159
Thanks again, but this one doesn't disable anything.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question