13 Replies Latest reply on Sep 29, 2006 7:47 PM by azadisaryev

    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.

        • 1. Re: Query of a Query I would think...
          azadisaryev Level 1
          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)
          • 2. Re: Query of a Query I would think...
            azadisaryev Level 1
            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>
            • 3. Query of a Query I would think...
              weswhite7 Level 1
              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
              • 5. Re: Query of a Query I would think...
                azadisaryev Level 1
                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...
                • 6. Re: Query of a Query I would think...
                  azadisaryev Level 1
                  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,
                  • 7. Re: Query of a Query I would think...
                    weswhite7 Level 1
                    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?
                    • 8. Re: Query of a Query I would think...
                      azadisaryev Level 1
                      hmm... let me check it out first... will be back...
                      • 9. Re: Query of a Query I would think...
                        weswhite7 Level 1
                        Ok, sounds good. Thanks for your help.
                        • 10. Re: Query of a Query I would think...
                          azadisaryev Level 1
                          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>
                          • 11. Re: Query of a Query I would think...
                            azadisaryev Level 1
                            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>
                            • 12. Re: Query of a Query I would think...
                              weswhite7 Level 1
                              You deserve like a cookie or something. I really appreciate your help though.
                              • 13. Re: Query of a Query I would think...
                                azadisaryev Level 1
                                happy i could help!