On 20 Nov 00, at 16:53, Dave Vause wrote:
> I've been banging my head against the wall over this one
> for two days...
Dave, this is the classic SQL "manager-employee" problem. In the textbook
example, the table holds employee records, each of which has a key called
Employee ID. Each record also has a field called Manager ID, which references
the Employee ID belonging to the employee's manager. The problem is, how to
create a list of employees, grouped by manager (in your case, a list of
characters, grouped by parent).
The answer is to use a recursive join. This means you join the table to itself
using aliases. To use the textbook example, we're logically joining the
Employees table in two different contexts: the employees context and the
managers context. So, we do something like this:
SELECT E.*
FROM Employees as E,
Employees as M
WHERE E.Manager = M.EmployeeID
GROUP BY E.Manager
In your case, it would be something like:
SELECT C.*
FROM Characters as C,
Characters as P
WHERE C.parentID = P.charID
GROUP BY C.userID, C.parentID
If you only want one user's characters, add a line to the WHERE clause that
reads something like
AND C.userID = #userID#
Have fun!
- Jeff
==============================================================
| Jeffrey S. Peters | "Specialization is for insects." |
| [EMAIL PROTECTED] | - Lazarus Long |
==============================================================
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
