#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    4
    Rep Power
    0

    HELP! category - subcategory relationship output


    Please, I cant think anymore...

    Due to this forum, I almost have my "application" working!!

    I need to display a main category, it's subcategories and their respective items.

    I have three tables:

    tbMemos (memoID, memoName)
    tbCategories (catID, catName, catParentCatID)
    tbMemoCategories (memoCatID, memoID, catID)

    tbMemos
    memoID memoName
    1 Good Morning
    2 Good Afternoon
    3 Good Evening

    tbCategories
    catID catName catParentCatID
    1 Leadership 0
    2 Roles 1
    3 Legislation 1
    4 Architecture 0
    5 Servers 4
    6 Acquisition 0
    7 Cell Phones 6

    etc...

    tbMemoCategories (ties these to the tbMemos table)
    memoCatID memoID catID
    1 1 4
    2 2 7
    3 3 2

    etc...

    DESIRED Output:

    Leadership (MAIN CATEGORY)

    Roles (SUBCATEGORY)
    memo
    memo
    memo

    Leadership (SUBCATEGORY)
    memo
    memo
    memo
    memo
    memo
    memo

    etc....

    My problem:
    I cannot figure out how to ouput as such. My output looks like this...

    Leadership (MAIN CATEGORY)

    Roles (SUBCATEGORY)
    memo
    Roles (SUBCATEGORY)
    memo
    Roles (SUBCATEGORY)
    memo

    Leadership (SUBCATEGORY)
    memo
    Leadership (SUBCATEGORY)
    memo
    Leadership (SUBCATEGORY)
    memo
    Leadership (SUBCATEGORY)
    memo
    Leadership (SUBCATEGORY)
    memo
    Leadership (SUBCATEGORY)
    memo

    etc...

    I've tried "group" by CatID in the output but I get:

    Leadership (MAIN CATEGORY)

    Roles (SUBCATEGORY)
    memo

    Leadership (SUBCATEGORY)
    memo

    etc... (not looping through the memos)

    Here is my code:

    <cfquery name="getMain" datasource="CIO">
    SELECT catName
    FROM tbcategories
    WHERE catID = #url.catID#
    </cfquery>

    <cfquery name="getMemos" datasource="CIO">
    SELECT tbMemos.memoID, memoName, tbCategories.catID, tbCategories.catParentCatID, tbCategories.catName, tbMemoCategories.catID, tbMemoCategories.memoID

    FROM tbMemos, tbCategories, tbMemoCategories

    WHERE tbMemos.memoID = tbMemoCategories.memoID AND
    tbMemoCategories.catID = tbCategories.catID AND
    tbCategories.catParentCatID = #url.catID#
    ORDER BY tbMemoCategories.catID
    </cfquery>


    <cfoutput query="getMain">
    <tr><td>#catName</td></tr>
    </cfoutput>

    <cfoutput query="getMemos" >
    <tr><td>
    #catName# #memoName#
    </tr>
    </cfoutput>

    </table>

    PLEASE HELP - WILL BE SO GRATEFUL!

    Comments on this post

    • Gran Roguismo agrees
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    99
    Rep Power
    12
    If I understand the problem correctly, this should do the trick...

    <cfoutput query="getMemos" group="CatID">

    #catName#

    <cfoutput>
    #memoName#
    </cfoutput>

    </cfoutput>

    Comments on this post

    • Gran Roguismo agrees
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    4
    Rep Power
    0

    Talking


    Thank You so much for replying with the correct solution!

    I mustve tried every combination but that one!

    Youre the best!

IMN logo majestic logo threadwatch logo seochat tools logo