I'm writing some code to suport a contact database, where a contact
can be either a person or a group. Groups can contain people directly
and can also contain other groups.

The (simplified) table structure looks like this:

PERSON
------
person_id number primary key

GROUP
-----
group_id number primary key

MEMBER_OF_GROUP
---------------
person_id number primary key 1 (foreign key -> PERSON.person_id)
group_id number primary key 2 (foreign key -> GROUP.group_id)

GROUP_IN_GROUP
--------------
subgroup_id number primary key 1 (foreign key -> GROUP.group_id)
supergroup_id number primary key 2 (foreign key -> GROUP.group_id)

I want to write a single query that will tell me if a person is a member
of a given group, whether as a direct member via the MEMBER_OF_GROUP
table or as an indirect member of a child group via the GROUP_IN_GROUP
table.

Anyone have suggestions?

--
John Gordon A is for Amy, who fell down the stairs
gordon (AT) panix (DOT) com B is for Basil, assaulted by bears
-- Edward Gorey, "The Gashlycrumb Tinies"

"John Gordon" <gordon (AT) panix (DOT) com> wrote

Take a look at the nested set model. With nested sets, you'll be able to
query for indirect children without coding up a recursive query.

"John Gordon" <gordon (AT) panix (DOT) com> wrote

Take a look at the nested set model. With nested sets, you'll be able to
query for indirect children without coding up a recursive query.

"John Gordon" <gordon (AT) panix (DOT) com> wrote

Take a look at the nested set model. With nested sets, you'll be able to
query for indirect children without coding up a recursive query.

get a copy of TREES & HIERARCHIES IN SQL and look at the nested sets
model.

get a copy of TREES & HIERARCHIES IN SQL and look at the nested sets
model.

get a copy of TREES & HIERARCHIES IN SQL and look at the nested sets
model.

In <2ev%j.3602$GT5.3283@trndny01> "David Cressey" <cressey73 (AT) verizon (DOT) net> writes:

Unfortunately I don't have the option of modifying the table structure
to represent the data as a nested set. I must work with the data as
given.

Is there a way to do this in standard SQL?

--
John Gordon A is for Amy, who fell down the stairs
gordon (AT) panix (DOT) com B is for Basil, assaulted by bears
-- Edward Gorey, "The Gashlycrumb Tinies"

In <2ev%j.3602$GT5.3283@trndny01> "David Cressey" <cressey73 (AT) verizon (DOT) net> writes:

Unfortunately I don't have the option of modifying the table structure
to represent the data as a nested set. I must work with the data as
given.

Is there a way to do this in standard SQL?

--
John Gordon A is for Amy, who fell down the stairs
gordon (AT) panix (DOT) com B is for Basil, assaulted by bears
-- Edward Gorey, "The Gashlycrumb Tinies"

In <2ev%j.3602$GT5.3283@trndny01> "David Cressey" <cressey73 (AT) verizon (DOT) net> writes:

Unfortunately I don't have the option of modifying the table structure
to represent the data as a nested set. I must work with the data as
given.

Is there a way to do this in standard SQL?

--
John Gordon A is for Amy, who fell down the stairs
gordon (AT) panix (DOT) com B is for Basil, assaulted by bears
-- Edward Gorey, "The Gashlycrumb Tinies"