Get Category, Subcategories and links summary with one query

[dimsis]

I have the following three tables:

links (fields: linkid, linkdescr)
category (fields: catid, catedescr)
subcategory (fields: catid, subcatid, subcatdescr)

How can i get all the categories and three (or more) of each subcategory and a summary of all links for each category, with one query?

I want to display something like this:

Category 1 (20)
Subcat1-1, Subcat1-2, Subcat1-3

Category 2 (17)
Subcat2-1, Subcat2-2, Subcat2-3

Category 3 (12)
Subcat3-1, Subcat3-2, Subcat3-3

etc etc...

 

[r937]

are links related to categories? subcategories? both?

please indicate how, i.e. where's the foreign key from links to whichever table


rudy

 

[dimsis]

Links are related to both category and subcategory via it's table's foreign key (catid, subcatid)

SELECT catedescr, (SELECT COUNT (*) FROM LINKS WHERE LINKS.catid=category.catid ) AS LINKS_COUNT
FROM category
ORDER BY catedescr

With this i can get all the category descriptions and the summary of the links inside them...
but not the subcategories...
(i'm trying this with sql server)

Thanx in advance

 

[r937]

okay, i thought so, it's just that you did not mention these columns the first time

are links "related to both category and subcategory" simultaneously? if both fields in a links row have a non-null value, are they consistent? i.e. do you link a link to a subcategory and its parent category too?

or is a link related to only a category or a subcategory, but never both? if that's the case, and a link is related to a category and a subcategory, does that mean two links rows? won't that duplicate the link description?

and what do you do if you have a subcategory that wants to have subcategories? do you make it a new category? how do you update the links links when you do that?

as you can imagine, the answers to these questions will affect how the query is built

also, you said "three (or more) of each subcategory" and this suggests that you do not want categories to be listed if the have fewer than three subcategories

if that's true, do you still want the category listed, with any links that are related to the category?

sorry to ask so many questions, but i prefer not to try to write sql until i am comfortble with the data structure


rudy

 

[dimsis]

First of all thank you for your patient till now.
Please check the following link:

http://www.developersites.com/

What i want to do, is excactly what you can see at the DIRECTORY section of this site.
I thought it could be easier to code if i use only two level's for categorization.
One main category and one subcategory related only to category via category's id.

Of course if you can post me a query to use unlimited categories and subcategories, i suppose with a use of a parentid into one and only table, it's a lot better.
I have used this method in the past, but i had trouble getting (analyze) the current path when i had a lot subcategories.
For example when i wanted to display :
Script > PHP > Databases > MySQL etc etc...

If there is an easy query to get the full path and another query for the directory display (like the one at

http://www.developersites.com),

of course i prefer this solution.

I can transform my tables to:
Category (categoryID, categoryDescription, ParentID)
Links (linksid, categoryID, linkdescription etc)

------

To my previous example with the use of only a category and one sub category i'm answering your questions:

are links "related to both category and subcategory" simultaneously? YES
if both fields in a links row have a non-null value, are they consistent? i.e. do you link a link to a subcategory and its parent category too? YES

or is a link related to only a category or a subcategory, but never both? (Always BOTH)

and what do you do if you have a subcategory that wants to have subcategories? do you make it a new category? how do you update the links links when you do that? Subcats can not have other subcats... two level only.

as you can imagine, the answers to these questions will affect how the query is built You are right

also, you said "three (or more) of each subcategory" and this suggests that you do not want categories to be listed if the have fewer than three subcategories No that means i want to list from zero the 3 or more subcategories for each category. For example if a category has only two subcategories i want to display only these two, but if it has ten subcats, i only want to display the CATEGORY.HOWMANYSUBCATEGSTOSHOW (i have added a field like this into categories that represents the number of subcategories the category shows...)

if that's true, do you still want the category listed, with any links that are related to the category? YES

sorry to ask so many questions, but i prefer not to try to write sql until i am comfortble with the data structure
Thanx again for your help and your patient! Hope to find a solution .

 

[r937]

since your links are always related to both a subcategory and the subcategory's parent category, then the link to the parent category is redundant, so take it out

this means you cannot have an entry linked to a category itself, which means that if the user clicks on a category, they must then select a subcategory to see any links, unless you list all links under the category as well as under their individual subcategories

to my way of thinking, none of the above is a good method, and as a user i would like to see links under both the category and subcategory level

CATEGORY.HOWMANYSUBCATEGSTOSHOW is not a bad idea, but i think this should not be set on each category, assuming that you wanted it for the purpose of only listing some of the subcategories on the main page, it would appear to be the same number (e.g. 3) for all categories on the main page and so it should be set in the main page code, which would return all subcategories, and only show a few of them

in my personal opinion, it is easier to code the category subcategory structure using a hierarchical relationship (i.e. the subcategory points to the parent category) even if all you want is two levels

> I can transform my tables to:
> Category (categoryID, categoryDescription, ParentID)
> Links (linksid, categoryID, linkdescription etc)

yes, i wholeheartedly agree

control the number of levels (e.g. 2) using your application logic, not the table structure

then you can change to three levels quite easily, whereas with your current structure, you will never be able to


rudy