[thelist] Re: Complex MySQL Query
Travis
travis at mediastill.com
Mon Aug 30 10:44:05 CDT 2004
Hi all, here's my situation
I have hierarchical data that can be an arbitrary number of levels deep. I'm
representing it with the adjacency list model. So there are 2 tables
Categories
id, parentid, title
and
DOCUMENTS
id, parentid, title, desc
Now, here is what I am already doing successfully - I can retrieve a
resultset which contains mixed results from both of these tables -
essentially a union statement, though because i am forced to use mysql 3.23
i am using the dummy table method. The query looks like this...
SELECT
IFNULL(documents.id, categories.id) AS id,
IFNULL(documents.parentid, categories.parentid) AS parentid,
IFNULL(documents.title, categories.title) AS title,
documents.desc,
IF(categories.id, 1, 0) AS isCategory
FROM _dummy AS D
LEFT JOIN documents ON (D.num = 0 AND documents.parentid = $parentID)
LEFT JOIN categories ON (D.num = 1 AND categories.parentid = $parentID)
WHERE D.num < 2 AND IFNULL(documents.id, categories.id) IS NOT NULL
This works like a charm. However, if possible I would also like to retrieve
an additional field which is a COUNT() of the immediate children (documents
and categories only one level deep) of each 'category' in the resultset.
Mysql 3.23 doesn't support subselects, but I'm fairly certain this is
possible with temporary tables. However, I'm not entirely sure how to go
about it. (Have been beating my head against this for awhile, grin)
If anyone has some suggestions, I'd be deeply appreciative. Currently I'm
just looping through the resultset and executing a separate COUNT() on
categories - which I'd prefer to avoid if possible.
Thanks in advance for your help.
Edit/Delete Message
ps - sorry if this message comes through twice - this is my first time using
thelist and i wasn't certain i sent correctly the first time.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.739 / Virus Database: 493 - Release Date: 8/16/2004
More information about the thelist
mailing list