Query of a Query I would think...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

weswhite7 - 28 Sep 2006 03:18 GMT

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.

Reply to this Message

Sabaidee - 28 Sep 2006 06:46 GMT

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# > #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# >
#qrySubcategories.name#</option>
    </cfloop>
</cfoutput>
</select>

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

Reply to this Message

Sabaidee - 28 Sep 2006 06:55 GMT

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# > #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# > #qrySubcategories.name#
    </cfloop>
</cfoutput>

Reply to this Message

weswhite7 - 28 Sep 2006 18:13 GMT

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

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

<cfquery name="qryCategories" username="#username#" password="#password#"
datasource="#datasource#">
            SELECT *
            FROM categories;
        </cfquery>
        <cfquery name="qryMainCategories" username="#username#"
password="#password#" datasource="#datasource#">
            SELECT *
            FROM categories
            WHERE subid = 0 OR subid = ''
            ORDER BY name;
        </cfquery>
        <select name="category">
            <cfoutput query="qryMainCategories">
            <cfquery name="qrySubcategories" dbtype="query">
                SELECT *
                FROM qryCategories
                WHERE subid = #qryMainCategories.id#
                ORDER BY name;
            </cfquery>
            <cfloop query="qrySubcategories">
            <option value="#qrySubcategories.id#">
            #qryMainCategories.name# > #qrySubcategories.name#
            </option>
            </cfloop>
            </cfoutput>
        </select>

Reply to this Message

weswhite7 - 29 Sep 2006 17:23 GMT

Anyone?

Reply to this Message

Sabaidee - 29 Sep 2006 19:10 GMT

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...

<cfquery name="qryCategories" username="#username#" password="#password#"
datasource="#datasource#">
            SELECT *
            FROM categories;
        </cfquery>
        <cfquery name="qryMainCategories" dbtype="query">
            SELECT *
            FROM qryCategories
            WHERE subid = 0 OR subid = ''
            ORDER BY name;
        </cfquery>
        <select name="category">
            <cfoutput query="qryMainCategories">
<cfset maincatname = qryMainCategories.name>
<cfset maincatid = qryMainCategories.id>
            <cfquery name="qrySubcategories" dbtype="query">
                SELECT *
                FROM qryCategories
                WHERE subid = #maincatid#
                ORDER BY name;
            </cfquery>
            <cfloop query="qrySubcategories">
            <option value="#qrySubcategories.id#">
            #maincatname# > #qrySubcategories.name#
            </option>
            </cfloop>
            </cfoutput>
        </select>

Reply to this Message

Sabaidee - 29 Sep 2006 19:25 GMT

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,

Reply to this Message

weswhite7 - 29 Sep 2006 21:02 GMT

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?

Reply to this Message

Sabaidee - 30 Sep 2006 03:24 GMT

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".

[b]NOTE: the piece of code below should replace the following code[/b]:

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

<cfif qrySubcategories.recordcount is 0>
    <option value="#mainid#">#maincatname#</option>
<cfelse>
    <cfloop query="qrySubcategories">
    <option value="#qrySubcategories.catid#">
    #maincatname# > #qrySubcategories.catname#
    </option>
    </cfloop>
</cfif>

Reply to this Message

weswhite7 - 30 Sep 2006 03:15 GMT

Ok, sounds good. Thanks for your help.

Reply to this Message

Sabaidee - 30 Sep 2006 03:26 GMT

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>

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / September 2006

Query of a Query I would think...