Query of a Query I would think...

[weswhite7]

So, I have a categories table. It has the name, and then the subid. If the subid is 0, it is a main category, otherwise it is a subcategory of the id it holds.

So, if we have books, and that books id is 1. Then we have non-fiction which is id of 2, but has a subid of 1. There is the example.

Now for my question. When I output these categories into a menu list, I want to list the main category so Books followed by Non-Fiction. So when it outputs into the list menu it looks somewhat like this.
Books > Non-Fiction
CDs > Rock
Movies > DVD

Where the subid of non-fiction, rock, and dvd is the id of books, cds, and movies.

Hope that makes sense! Any help would be great.

[azadisaryev]

one way to do it will be:
(note: in the qryCategories i am assuming your main categories have a subid of 0 or empty string)

<cfquery name="qryCategories" datasource="mydsn">
SELECT *
FROM tblCategories
WHERE subid = 0 OR subid = ''
ORDER BY name;
</cfquery>

<cfoutput query="qryCategories">
<cfquery name="qrySubcategories" datasource="mydsn">
SELECT * FROM tblCategories WHERE subid = #qryCategories.id# ORDER BY name;
</cfquery>
<cfloop query="qrySubcategories">
#qryCategories.name# &gt; #qrySubcategories.name#
</cfloop>
</cfoutput>

if by "outputs into the list menu" you mean a drop-down select box, then the second part of the code will be:

<select name="select1" size="1">
<cfoutput query="qryCategories">
<cfquery name="qrySubcategories" datasource="mydsn">
SELECT * FROM tblCategories WHERE subid = #qryCategories.id# ORDER BY name;
</cfquery>
<cfloop query="qrySubcategories">
<option value="#qrySubcategories.id#">#qryCategories.name# &gt; #qrySubcategories.name#</option>
</cfloop>
</cfoutput>
</select>

(note: the value of selected item in the list above is set to subcategory id)

[azadisaryev]

if you insist on using QoQ, then:

a) 1 QoQ using <cfif> loop in output:
<cfquery name="qryCategories" datasource="mydsn">
SELECT *
FROM tblCategories
ORDER BY name;
</cfquery>

<cfoutput query="qryCategories">
<cfif qryCategories.subid is 0 OR qryCategories.subid is "">
<cfquery name="qrySubcategories" dbtype="query">
SELECT *
FROM qryCategories
WHERE subid = #qryCategories.id#
ORDER BY name;
</cfquery>
<cfloop query="qrySubcategories">
#qryCategories.name# &gt; #qrySubcategories.name#
</cfloop>
</cfif>
</cfoutput>

or:

b) 2 QoQs:
<cfquery name="qryCategories" datasource="mydsn">
SELECT *
FROM tblCategories;
</cfquery>

<cfquery name="qryMainCategories" dbtype="query">
SELECT * FROM qryCategories WHERE subid = 0 OR subid = '' ORDER BY name;
</cfquery>

<cfoutput query="qryMainCategories">
<cfquery name="qrySubcategories" dbtype="query">
SELECT *
FROM qryCategories
WHERE subid = #qryMainCategories.id#
ORDER BY name;
</cfquery>
<cfloop query="qrySubcategories">
#qryMainCategories.name# &gt; #qrySubcategories.name#
</cfloop>
</cfoutput>

[weswhite7]

I have attached the code I am using. By the way, thank you very much for your help.

I tried all of your methods and they work but with one small problem. It is putting the main category of apartments out in front of all the subcategories, even though some subcategories are not under that main category.

It is looking like this.
Apartments > Arts
Apartments > Jobs
Apartments > PCs
Apartments > XXX

When it should be like:
(id of 1)Apartments > One Bedroom (subid of 1)
(id of 2)Jobs > On Campus (subid of 2)
(id of 2)Jobs > Downtown (subid of 2)
and so on.


and so on. I don't know if I copied something down wrong, or if its my mistake or whatever. I attachd the code, so I hope you can help me out again here!

Thanks Sabaidee

[weswhite7]

Anyone?

[azadisaryev]

ok, try this. it is pretty much the same except for a couple small alterations...
basically, the name and id of main category are now <cfset> as new variables insdie the <cfoutput>.
i have not yet tested this, but will do now...

[azadisaryev]

just tested it and it work fine!

if you change the names of variables assigned inside the <cfoutput> of the qryMainCategories, make sure they do not match names of any columns in your table!

in my test database i had the subid column named maincatid, and at first the code was returning wrong results, because of <cfset maincatid = qryMainCategories.id> - i used same name for the variable as one of the columns... as soon as i changed it to mainid it work fine.

cheers,

[weswhite7]

Marvelous. Now my only question is there are some categories that do not have subcategories. These are not getting outputted now. How would I go about doing that?

[azadisaryev]

ok, the piece of code below will output main categories IF they have no sub-categories.
i.e. if an "Apartments" category has sub-categories, it will be included in the list as "Apartments > sub-category1", "Apartments > sub-category2", etc. there will be no single "Apartments" option in the list if Apartments has sub-categories.
if Apartments category DOES NOT have any sub-categories, it will be included in the list as "Apartments".

NOTE: the piece of code below should replace the following code:

<cfloop query="qrySubcategories">
<option value="#qrySubcategories.id#">
#maincatname# &gt; #qrySubcategories.name#
</option>
</cfloop>

[azadisaryev]

hmm... let me check it out first... will be back...

[weswhite7]

Ok, sounds good. Thanks for your help.

[azadisaryev]

if you want to display single main categories whether they have any sub-categories or not, then just add the following line:

<option value="#mainid#">#maincatname#</option>

after the line

<cfset mainid = qryMainCategories.catid>

[weswhite7]

You deserve like a cookie or something. I really appreciate your help though.

[azadisaryev]

happy i could help!