[thelist] MySQL query with two left joins

Joshua Olson joshua at waetech.com
Mon Jun 30 10:03:21 CDT 2003


----- Original Message ----- 
From: "Sarah" <poohbear at designshift.com>
Sent: Monday, June 30, 2003 10:50 AM


> 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.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168



More information about the thelist mailing list