[thelist] MySQL query with two left joins

Sarah poohbear at designshift.com
Mon Jun 30 10:38:51 CDT 2003


> > CATEGORIES
> > ----------
> > CategoryID
> > ParentCategoryID
> > Title
>
> > In the admin system for the site, I am trying to output a list of child
> > categories of one parent. I would like to include the count of the
> > categories' documents and the count of the categories' sub-categories in
> > this list as well, but I can't quite figure out how to make my left joins
> > cooperate. Here is the query I am using:
>
>Sarah,
>
>This can be a very tough request.  One technique that I've used was to store
>a lineage in the CATEGORIES.  Example:
>
>CategoryID, ParentCategoryID, Title, Lineage
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>1, 0, 'Cat 1', ','
>2, 0, 'Cat 2', ','
>3, 1, 'Cat1.1', '1,'
>4, 1, 'Cat1.2', ''
>5, 3, 'Cat1.1.1', '1,3,'
>6, 3, 'Cat1.1.2', '1,3,'
>
>Basically, lineage is a string containing a comma delimited list of all
>parents, starting at the root, which a trailing comma.
>
>This way, if you wanted to quickly return all categories that are
>sub-categories of a category x, simply find all categories whose lineage
>begins with category x's lineage PLUS the id of the category x.
>
>Example, to find all children on 'Cat 1' using the sample data above:
>
>SELECT *
>FROM CATEGORIES
>WHERE lineage LIKE '1,%'
>
>Notice that I padded the lineage column with a trailing "," so that the LIKE
>clause would not return erroneous data.  If the comma wasn't in there and I
>search on LIKE'1%' then lineages starting with "10," would be returned if
>they existed.
>
>Since you'll now have the means to find all child categories, performing a
>count of items in those categories should be much simpler.


Sorry, maybe I didn't explain well enough (it is a complex site). Let's say 
this is a sample of the hierarchy:

Category 1
   Category 1 a
   Category 1 b
Category 2
   Category 2 a
     Category 2 a I
     Category 2 a II
   Category 2 b
   Category 2 c

What I am trying to do is, for example, print a list of the sub-categories 
of Category 2, which would include Category 2 a, Category 2 b, and Category 
2 c. I don't want to include the children of the children. This list should 
then include a count of the sub-categories and documents contained in 
Category 2 a, Category 2 b, and Category 2 c, respectively.

Hope this makes a bit more sense.

Sarah




More information about the thelist mailing list