SQL query on heirchical data

[Curtis Poe]

Hi, I've been trying to work out how to get this done, but I can't seem to
figure it out. Given the following table definitions (simplified for
clarity), I need to accomplish the following:

For a given categoryID, I need to select all productIDs and productNames
that exist in the product_contact_currency table:

CREATE TABLE category_product (
categoryID int,
productID int
)

CREATE TABLE products (
productID int,
productName varchar
)

CREATE TABLE product_contact_currency (
productID int,
contactID int,
currencyID int
)

Ordinarily, this would be straightfoward:

SELECT p.productID, p.productName FROM products p
INNER JOIN category_product cp ON cp.productID = p.productID
WHERE p.productID IN (SELECT pcc.productID FROM product_contact_currency
pcc)
AND categoryID = 5

Unfortunately, the above presupposes that all productIDs are in the
category_product table. In reality, we have products (parents) and
subproducts (children). All of them are listed in the products table and
there is another table, "children", that maps child products to parent
products. A parent having a child is completely optional. Here's the
children table.

CREATE TABLE children (
productID int,
childID int
)

So, if I have a child, with an ID of 7, whose parent has an ID of 1, a
productID of 1 will be in the category_product table, but the child, 7, will
not be in that table. So, I need to figure out how to modify the above
query to also include all products that are children and whose parent is in
the correct category.

Any and all suggestions are welcome.

--
Curtis "Ovid" Poe, Senior Programmer, ONSITE! Technology
Someone asked me how to count to 10 in Perl:
push @A, $_ for reverse q.e...q.n.;for(@A){$_=unpack(q|c|,$_);@a=split//;
shift @a;shift @a if $a[$[]eq$[;$_=join q||,@a};print $_,$/for reverse @A

[BP Margolin]

Curtis,

Check out the section "Expanding Hierarchies" in the SQL Server Books
Online.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Curtis Poe" <c...@onsitetech.com> wrote in message
news:aan38q$o...@dispatch.concentric.net...