Hello,

I'm trying to create some basic Crystal reports to run against the database. One of the main reports that we need to create is, a product and the category that it is attached to.

There seems to be 4 tables at play here:

- Products
- Categories
- CatalogNodes
- CategoryParents

Below is the SQL attaching the tables, at least three of them:

SELECT
dbo.ac_Products.ProductId,
dbo.ac_Products.Name,
dbo.ac_Products.Sku,
dbo.ac_CatalogNodes.CatalogNodeId,
dbo.ac_Categories.CategoryId,
dbo.ac_CatalogNodes.CategoryId,
dbo.ac_Categories.Name
FROM
dbo.ac_Products
INNER JOIN dbo.ac_CatalogNodes ON dbo.ac_Products.ProductId = dbo.ac_CatalogNodes.CatalogNodeId
INNER JOIN dbo.ac_Categories ON dbo.ac_CatalogNodes.CategoryId = dbo.ac_Categories.CategoryId


Everything works fine until you bring the CategoryParents table into the picture, it seems that if you have a product that is nested 2 levels deep, then it would have multiple parents. So if, I check the main category just one time, under category B, and category B is a child of Category A, then in reality the product would have two parent categories as opposed to one, even though category A is not checked.

I just want to make sure that this is correct, since linking the CategoryParents table to the above SQL query will create multiple instances of the same product.

Am I correct on this?

Many Thanks

Edited by user Wednesday, March 4, 2020 12:29:35 AM(UTC)  | Reason: Not specified