[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