MySQL query help with categories and subcategories

Ok, I’m hoping someone can help me as I’m pulling my hair out of this one and feeling like a general idiot. I’m building an employee information listing displaying all departments alphabetically along with employees (sorted alphabetically by last name) in those departments. This of course is very straight forward and not an issue until the client decided they wanted to break out several departments into sub departments.

I have two tables:

[code]Categories
cId | cParentId | CategoryName | CategoryDescription

People
pId | pCategory | pFirstName | pLastName | pPhone | pEmail [/code]

I added cParentId for the requirement for sub departments.

Here is my current query:

$Query = "SELECT * FROM  `People` LEFT JOIN  `Categories` ON  `pCategory` =  `cId` ORDER BY  `CategoryName` , `cParentId` , `pLastName` ASC";

The problem occurs with the sales department that is broken up into several regions (the sub departments):

Records in Category

| cId | cParentId | CategoryName | 1 | 0 | Human Resources | 4 | 0 | Call Center | 31 | 0 | Sales | 40 | 31 | Central Region | 42 | 31 | North Region

As you can see, when I run my query, people that should be listed under Sales in Central Region are listed below Call Center. How can I get my order right on my query with the Category Name is sorted alphabetically only on the main categories. My output should be something like:

Call Center
Human Resources
Sales
Central Region
North Region

You could try using the following query:

$Query = "SELECT * FROM  `Categories` LEFT JOIN  `People` ON  `pCategory` =  `cId` ORDER BY  `CategoryName` , `cParentId` , `pLastName` ASC";

But I’m not sure if that’s going to work very well. If not, I’m afraid you’re going to have to use a more PHP-oriented approach.

Sponsor our Newsletter | Privacy Policy | Terms of Service