SQL querying over multiple tables

Question

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

I have 2 tables like this.

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?

Answer 1

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.

Answer 2

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.