asked on

ID and parentID - how to sort?

Hi, this is a table I've got (if it doesn't look good, paste it into notepad and use system as font... )

tblProjects
-----------------------------------
| ID | parentID | content |
|----------------------------------
| 1 | null | blabla |
| 2 | 1 | blabla |
| 3 | 1 | blabla |
| 4 | null | blabla |
| 5 | 4 | blabla |
| 6 | 4 | blabla |
| 7 | 4 | blabla |
| 8 | null | blabla |
| 9 | 1 | blabla |
| 10 | 1 | blabla |
| 11 | 4 | blabla |
| 12 | 4 | blabla |
-----------------------------------

These are all different projects and instances of projects. Where ID=1, we have a Mother project. ID 2 and ID 3 are children of that Mother Project. Same is found for Mother project 4 and mother project 8.

I want to send a request to the database whereas it replies with the entire list; listing first the mother project, then the parent projects, under each mother object. The result would be something like this:

-----------------------------------
| ID | parentID | content |
|----------------------------------
| 1 | null | blabla |
| 2 | 1 | blabla |
| 3 | 1 | blabla |
| 9 | 1 | blabla |
| 10 | 1 | blabla |
| 4 | null | blabla |
| 5 | 4 | blabla |
| 6 | 4 | blabla |
| 7 | 4 | blabla |
| 11 | 4 | blabla |
| 12 | 4 | blabla |
| 8 | null | blabla |
-----------------------------------

Get my picture? How can I do this?

Any help appreciated,

Kind regards,
Simon

appari

select ID, case parentID when null then ID else parentID end, content from tblProjects
order by 2,1

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NiTech

ASKER

Thank you- this worked excellent.

This is an example of my output:

SortID ID ParentID enDescription
----------- ----------- ----------- ----------------------
195 195 NULL Autonomy Implementation
226 226 NULL Additional HelpDeks resource Q1
230 230 NULL Accenture
235 235 NULL Alcatel 4980 Implementation
296 296 NULL Application Support/ Upgrade OOS
348 348 NULL ATM Workshop
354 354 NULL Annual leave
354 653 354 Annual Leave Calloff 1
354 654 354 Annual Leave Calloff 1
354 655 354 Annual Leave Calloff 1
361 361 NULL Application Upgrade - item 001
402 402 NULL Autonomy

As you can see, it is now very easy to determine whether or not the row is a child or a parent, simply by checking for existance of a ParentID.

Thank you very much.

Kind Regards,
Simon