Thread: Database table question

Is there a direct connection between Categories and Sections(Departments) in the database, or do you need to use the product tables (ProductCategory, ProductSection) in order to connect them?

I have been trying something like below, but it is not working:
Select CategoryID, Name, ImageFilenameOverride From Category Where ParentCategoryID = 2227 and CategoryID IN(Select CategoryID from ProductCategory Where ProductID IN(Select ProductID From ProductSection Where SectionID = @SecID))

I am getting all of the categories with a parent category of 2227 but it is not filtering at all on the sectionID. and yes, @SecID is getting a value, before you ask. Even when I hard code a number in there I get the same results.

I am using something similar with manufacturers and it is working correctly, but I am obviously missing something with sections. If any SQL masters are out there, I could really use some help.

thanks

Your query works. But it depends on the data. If I break it down into its parts I get the following results:

Code:

		Select ProductID 
		From ProductSection 
		Where SectionID = 1

I've used SectionID=1 as a known value, but you are wanting to use a variable value. I get the following productid results for that query.

Code:

975
2970
971
973
974
976
977
972

If I then expand the query to add-in the next part, like this:

Code:

	Select CategoryID 
	from ProductCategory 
	Where ProductID IN (
		Select ProductID 
		From ProductSection 
		Where SectionID = 1
	)

I get the following categoryid results:

Code:

412
412
412
412
412
412
412

I would then have to know what the ParentCategoryID for my CategoryID=412 is:

Code:

select parentcategoryid from Category where CategoryID=412

Which in my case is 396.

Therefore, based on your original query, the only query that would work for me is:

Code:

Select CategoryID, Name, ImageFilenameOverride 
From Category 
Where ParentCategoryID = 396 
and CategoryID IN (
	Select CategoryID 
	from ProductCategory 
	Where ProductID IN (
		Select ProductID 
		From ProductSection 
		Where SectionID = 1
	)
)

Which gives, for us:

Code:

412	Belt Barriers & Posts	{blank}

This tells me, all products that live in section (department) 1 also exist in category 412 and has a parent category of 412.

It's all about the data.

Thanks for your reply. I am glad to hear it is working, now I just need to figure out what is not. Maybe I can clarify a little more and you can see where I am going askew.

This is for a Cell phone accessory company (batteries, chargers, faceplates, etc).
CATEGORY: Models (ID 2227) has 1056 SubCategories which are the actual phone models (iphone4, LG Vortex, HTC HD7, etc).

SECTION: Carriers (ID 72) has 13 SubSections which are the actual carriers (AT&T, Verizon, etc)

SubSection 81 is Helio, which should only have 1 model associated with it, the Alcatel tribe OT-981A (SubCategory ID 3230)

Number of products associated with that model should be 48

So if I break it down like you did here is what I am getting.
Select ProductID
From ProductSection
Where SectionID = 81

gives me 48 results

Select CategoryID
from ProductCategory
Where ProductID IN (
Select ProductID
From ProductSection
Where SectionID = 81
)

gives me 24906 results, but if i change it to DISTINCT CategoryID I get 1032 results. However if I do:

SELECT CategoryID
FROM ProductCategory
WHERE (CategoryID = 3230)

I get 48 result which is what I should be getting.

and finally:
Select CategoryID, Name, ImageFilenameOverride
From Category
Where ParentCategoryID = 2227
and CategoryID IN (
Select CategoryID
from ProductCategory
Where ProductID IN (
Select ProductID
From ProductSection
Where SectionID = 81
)
)

gives me 1030 results

So something is just not right trying to filter data in the ProductCategory Table. I am just going to keep working away at trying to figure this out, but if you can see where it is getting all buggered up, let me know.

Thanks again.

UPDATE: So as I keep staring at this issue, I finally realized why the 1032 with the DISTINCT call in the ProductCategory table is correct, based on on the query, even though it is not what I actually want. So then the question is, is it even possible to get the information that I need? It seems like there needs to be another subquery to actually get this to work.

Thinking out loud: So the first query returns 48 products. The second query takes each of the products from the first query and finds the associated category, but some products are attached to multiple categories, which is why I get 24906 results (or 1032 DISTINCT CategoryID's). So I need a subquery here which takes the results from the second query and somehow determines if it is associated with SectionID 81. The problem is there is no way (that I can think of) that you can use the results from the second query and query them against the first in order to find the categoryID I am looking for.

If it was a one to one relationship (one product for one category) like I believe esedirect gave earlier, I could see how this could work, but having a many product many category relationship seems to make it several orders of magnitude more difficult.

Does any one see any way possible for this to work?

SubSection 81 is Helio, which should only have 1 model associated with it, the Alcatel tribe OT-981A (SubCategory ID 3230)

No, I don't think that works.

A Product belongs to 0-to-many Section(s) via the ProductSection table, and can also belong to 0-to-many Catgegory(ies) via the ProductCategory table.

You've said that the Alcatel is a Category (a sub-Category of Models), but you haven't mentioned if it is a product as well. In fact you say that the website is for accessories.

There is no direct relationship between Category and Section, you need derive one from the other through which Product(s) it lists.

You can have a look at the entity relationship diagram in product.pdf for the relationships between Product, Category and Section. I have reduced the number of columns in the tables on purpose.

esedirect - again thank you for taking the time to respond and for the table layout. I will try to clarify an little more in the hopes that there is a way to make this work.

I have a Category (Models) which has 1000+ subCategories (various phone models) like so:

Models
Alcatel tribe OT-981A
iPhone4
.....
LG Chocolate

I have two Sections, Accessories and Carriers, with SubSections, like so...

Accessories
Cases
Batteries
....
chargers

Carriers
AT&T
Verizon
.....
Sprint

There are also Manufacturers, but that isn't pertinent to this discussion.
And finally we have products. Products can be associated with Sections and with Categories like so...

Product: X1A Phone Charger
Category: Models SubCategories: Alcatel tribe OT-981A, iPhone4, LG Chocolate
Section: Accessories SubSection:Phone Chargers
Section: Carriers SubSection: AT&T, Verizon, Sprint

So a product (X1A Phone Charger) can belong to multiple Categories and multiple sections and subsections.

The functional goal of this is on the left we have three menus, one for Accessories, one for Carriers and one for Manufactuers. When you click on Carriers, it shows you a list of call carriers. If for example you select Helio, the page should bring up all models associated with that carrier. The manufacturers menu works correctly, I assume because a product can only have one Manufacturer, and the Accessories menu works correctly also because when you click on "CHARGERS" it is bringing up a list of products, not models and there is a direct relationship between products and Sections.

In the example from our previous discussion using Helio, the query for the ProductSection Table returned 48 results, so there are 48 products that have a relationship with that section. So lets take two of those products as an example: ProductID 12345 and 67890

The query for the ProductCategory takes each of the 48 products from the previous query and finds all of the categories associated with the products. So using the previous example, we now have results like so...

ProductID CategoryID
12345 100
12345 200
12345 300
12345 400
67890 500
67890 300
67890 700

The CategoryID we really want is 300, but because of the one to many relationship, we get others as well. We can't use a DISTINCT or GROUP BY on either column because it will not accurately show the results.

So the final query for the Category table takes all of the Categories from the ProductCategory result set and then looks to see what ones have a ParentCategoryID of 2227, but we don't have any way to tell it that we just want it to use CategoryID 300.

I cannot figure out any way to filter the result of the ProductCategory query in order to make it correlate to a specific section. I honestly don't see any way to derive the data that I am trying to get short of building a new table and manually entering in relationships between the categories and sections. But that means I need to go in and manually add a relationship anytime the customer adds a model or carrier and the powers that be would like it to be automatic so we don't have to deal with it.