nested loop, 2 MySQL tables

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Chris
    #1

    nested loop, 2 MySQL tables

    I have a set of nested links, and at one time each category one had all the
    same subcategories. Now that has changed - each category has different
    subcategories. I have a tree menu, but now it just shows all subs under
    each category, of course. I have 2 tables - one for categories, one for
    subcategories - they are related by the categoryID. I think I have the SQL
    fine (have tried several different versions - see most recent/uncluttered
    below), but how do I get it to work correctly in the tree loop?

    SELECT DISTINCT cat.catName, cat.catDesc, subcat.subcatNa me
    FROM cat, subcat
    WHERE cat.catID = subcat.catID;

    Tree example:
    cat.catName(1)
    subcat.subcatNa me(1)
    subcat.subcatNa me(2)

    cat.catName(2)
    subcat.subcatNa me(3)
    subcat.subcatNa me(4)


    I'm sure it must be simple, and I'm just having a slow brain day...

    Thanks,
    Chris


    Tags: None
    • Paul Lautman
      #2
      Re: nested loop, 2 MySQL tables

      Chris wrote:[color=blue]
      > I have a set of nested links, and at one time each category one had
      > all the same subcategories. Now that has changed - each category has
      > different subcategories. I have a tree menu, but now it just shows
      > all subs under each category, of course. I have 2 tables - one for
      > categories, one for subcategories - they are related by the
      > categoryID. I think I have the SQL fine (have tried several
      > different versions - see most recent/uncluttered below), but how do I
      > get it to work correctly in the tree loop?
      > SELECT DISTINCT cat.catName, cat.catDesc, subcat.subcatNa me
      > FROM cat, subcat
      > WHERE cat.catID = subcat.catID;
      >
      > Tree example:
      > cat.catName(1)
      > subcat.subcatNa me(1)
      > subcat.subcatNa me(2)
      >
      > cat.catName(2)
      > subcat.subcatNa me(3)
      > subcat.subcatNa me(4)
      >
      >
      > I'm sure it must be simple, and I'm just having a slow brain day...
      >
      > Thanks,
      > Chris[/color]

      Does this help:
      fw_error_www
      http://dev.mysql.com/tech-resources/articles/hierarchical-data.html



        Comment

        • lorento
          #3
          Re: nested loop, 2 MySQL tables

          SELECT cat.catName, cat.catDesc, subcat.subcatNa me
          FROM cat
          LEFT JOIN subcat ON subcat.catID = cat.catID
          ORDER BY catID ASC

          --
          http://blog.deshot.com

          http://www.padbuilder.com


          Chris wrote:[color=blue]
          > I have a set of nested links, and at one time each category one had all the
          > same subcategories. Now that has changed - each category has different
          > subcategories. I have a tree menu, but now it just shows all subs under
          > each category, of course. I have 2 tables - one for categories, one for
          > subcategories - they are related by the categoryID. I think I have the SQL
          > fine (have tried several different versions - see most recent/uncluttered
          > below), but how do I get it to work correctly in the tree loop?
          >
          > SELECT DISTINCT cat.catName, cat.catDesc, subcat.subcatNa me
          > FROM cat, subcat
          > WHERE cat.catID = subcat.catID;
          >
          > Tree example:
          > cat.catName(1)
          > subcat.subcatNa me(1)
          > subcat.subcatNa me(2)
          >
          > cat.catName(2)
          > subcat.subcatNa me(3)
          > subcat.subcatNa me(4)
          >
          >
          > I'm sure it must be simple, and I'm just having a slow brain day...
          >
          > Thanks,
          > Chris[/color]

            Comment

            Previous template Next
            Working...