Agonizing Query.. Possible?

[webmigit]

I have a category system like this, its two levels deep, and goes no deeper:

A
1
Item
Item
Item
2
Item
Item
Item
3
Item
Item
Item
4
Item
Item
Item
5
Item
Item
Item
B
1
Item
Item
Item
2
Item
Item
Item
3
Item
Item
Item
4
Item
Item
Item
5
Item
Item
Item

A,B,C,D,E,F all have 5 child categories, and all child categories have three items inside them.. If they don't have three items inside them, then I want <CFSET Alert=&quot;Red&quot;>...

Here's what I'm thinking.. Each Item ID would be something like this

A1_0001
A2_0020
A3_0057
B6_0121

The first character is the TL Category, the second is the parent category, the underscore is meaningless and the last four digits are an id of the actual item..

So if in C-5 the user selects &quot;Christmas&quot; with an id of 238, the id sent to the database is:

C5_0238 would be sent to the database.. Now I know, you're probably thinking this is easy.. and it is, but the problem is in the next step, I don't want to run 30 queries to make sure the user has selected 3 items from each Sub Category.. Is there a way in CFQUERY to say this maybe?

Because of this dilemna.. I have no definite table structure up.. So anyway to make this work is fine.. or if you can think of a better way, it would be very much appreciated.

Thanks
Tony Hicks
AIM: Clickthru Bible | YIM: Webmigit | MSN: webmigit@hotmail.com [ Founder of <A href=&quot;

http://www.bibleclicks.com&quot;>Bibleclicks.com</A>,

online bible. ]

 

[CFDude]

Tony,
I understand the way it is all set up, but I am not too clear on what exactly you want to find out -- are you wanting to see how many items are in each sub category or are you trying to ensure that the user picks 3 items from each top level category?

 

[Guest_imported]

Why do you think that you need 30 queries? Give us a sample query and explain why you need 30 of them.

As to the table structure, it should be independent of the types of queries that you will do. That is, it should be independent of the problem. Therefore, you set up your tables the same way (generally), regardless. If you use third normal form, you should be able to get just about any information out of it that you want, with a minimum of difficulty. Without it, it is highly unlikely that you will be able to do what you want without using 30 queries. So, always begin by putting your data into third normal form. If you don't know what that is, then you know exactly what you should be studying. This is essential material for anyone who designs databases. Here is what third normal form looks like for your data:

Note: This design assumes that
- each category has multiple subcategories, and each subcatagory belongs to only one category
- each subcategory has multiple items, and each item belongs to only one subcategory

If these assumptions are not correct, you will need a different data structure.

Create these tables before you attempt to solve your query problem:

-------------------------------------------------
create table category
(
category_name char(1),
constraint ist_pk primary key (category_name)
);
create table subcategory
(
category_name char(1),
subcategory_name char(1),
constraint ist_pk primary key (category_name, subcategory_name),
constraint fk_catname FOREIGN KEY (category_name) references category
);
create table items
(
category char(1),
subcategory char(1),
item_name char(128),
constraint fk_subcat foreign key (category, subcategory) references subcategory (category_name, subcategory_name)
);
--------------------------------------------------
Now populate your tables. You won't need (and you should not use) the two letter prefix to the item name that you are currently using. Once you have all the tables populated, you're ready to query. I don't have time to develop the query for you right now, but the SQL SELECT statement will involve

- using the aggregate function count()
- the GROUP BY clause (this is not the CF group attribute)
- the HAVING clause
- the IN clause
- possibly using IN or EXISTS

The goal is to come up with a query that returns 30 records (6 categories X 5 subcategories) with these fields:

category
subcategory
item_count

Then using CF, you can loop through the records and determine if in any records item_count is not equal to 3.

I know that this is not complete, but it should get your started.