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