Take the 2-minute tour ×

I don't have much experience in SQL so I think this is not a dumb question.

I have 2 tables like this.

alt text

A .. G are the members of a hierarchy.

Now my requirement is as follows.

I need to filter out the members which has status = 0 from Members table.

But, If the selected set contains children which has a parent with status = 0, Ignore the child and select only the parent.

As an example, in the above case the set with 0 status = {B,C,D,E,F,G} But C,D,E,F has status 0 parents. So my result should be {B,G}

Can I do this just by using SQL and related technologies? (i.e In database layer. I don't want to query into the data structures and then iterate. Can I write a single query for this?)

I will add some more examples if the question is confusing?

share|improve this question
Interesting question. Now, do you care about the whole relationship tree, or only about direct ancestor? E.g. if the grandparent (or great-grandparent, etc.) has status = 0, do you want the child shown? In your example, assuming everyone except B = 1, and B = 0; do you want to show F, even though it has an ancestor with status 0, several levels above? –  Piskvor Sep 21 '10 at 8:09
@ Piskvor : If D = 1 in above example it is still sufficient to get {B,G}. I don't want to get {B,E,G} as a result. (If a parent has state 0, then it should be in the set but not any of its children.) –  Chathuranga Chandrasekara Sep 21 '10 at 8:22
Not an answer, but combine this with @gandjustas's answer and you'll get what you need: msdn.microsoft.com/en-us/library/ms186243.aspx –  AlexanderMP Sep 21 '10 at 11:00
add comment

2 Answers

up vote 1 down vote accepted
select m.ID from Members m
left join Relations r on r.Child = m.Id
left join Members p on r.Parent = p.Id
where m1.Status = 0 and p.Status <> 0

But i think there will be a recursive query.

share|improve this answer
This would result in B only. Still G is a member with status 0 and I need to get that listed because it is not a child of anyone. What is the best way to do this? Just "UNION"ing? –  Chathuranga Chandrasekara Sep 21 '10 at 8:32
select m.ID from Members m left join Relationships r on r.Child = m.Id left join Members p on r.Parent = p.Id where m.Status = 0 and p.Status <> 0 union select ID from Members Where Status=0 AND NOT ID IN (SELECT Child from Relationships union SELECT Parent from relationships) –  Chathuranga Chandrasekara Sep 21 '10 at 8:53
This answer is just plain incorrect. Left outer join is one way to express a not exists; however, the outer join criteria would have to include p.Status = 0 and the where clause would have to restrict the result to p.Status is null. –  bbadour Sep 21 '10 at 14:49
Yes, there should be m.Status = 0 and (p.Status <> 0 or p.Status is null) in Where clause. –  gandjustas Sep 22 '10 at 7:05
add comment

Yes, you can do what you need using just SQL.

Since parent and child must both have status 0 to be considered, let me rephrase your query:

Return all members with status 0 that have no parent(s) with status 0.

There are several different ways to write a "not exists" query like the above. One example is:

select *
from Members
where ID not in (
    select Child
    from Relationships r join Members m on r.Parent = m.ID

Things would get a little more complicated if you were returning parents regardless of status, but you are not.

share|improve this answer
add comment

Your Answer


