[thelist] SQL - ORDER with two parameters

[Adriano Castro]

    Hi,

    1st post after a lot of reading. Here goes...


    Take into consideration the following tables.

Table 1: doc_list

| id | id_type | reference |
----------------------------
| 41 |    2    |     c     |
| 42 |    2    |     a     |
| 43 |    2    |     e     |
| 44 |    2    |     e     |
| 96 |    1    |     21    |
| 97 |    1    |     14    |
############################


Table 2: doc_relationships

| id | id_doc | id_relationship | id_referrer |
-----------------------------------------------
| 26 |   41   |        1        |      96     |
| 27 |   42   |        1        |      96     |
| 28 |   43   |        1        |      96     |
| 29 |   44   |        1        |      97     |
###############################################


    I want to select all documents from table 1 that are of id_type = 2
and want to sort them by their reference. For this matter the following
SQL Query would work perfectly:

    "SELECT * from doc_list WHERE id_type = 2 ORDER BY reference ASC"

    This would render the following:

| id | id_type | reference |
----------------------------
| 42 |    2    |     a     |
| 41 |    2    |     c     |
| 43 |    2    |     e     |
| 44 |    2    |     e     |
############################


    However, I want the sorting to be done in a more complex way.

    As you may have notice, in table 2 I establish relationships betwee
n
the documents. A relationship (id_relationship) of 1 means that id_doc is
a child of id_referrer. This means that the full reference for, for
example, document doc_list.id = 41 is, actually, 21c (reference of parent
+ reference of self).

    That said, I want the sorting of the docs with id_type = 2 to be
done not just by their reference but by their parent's reference aswell. I
want the SQL query to render this:

| id | id_type | reference |
----------------------------
| 44 |    2    |     e     |  -> comes 1st cuz its parent reference is 11
| 42 |    2    |     a     |  -> comes 2nd cuz its parent reference is 21
| 41 |    2    |     c     |  -> parent's ref is 21
| 43 |    2    |     e     |  -> parent's ref is 21
############################


    How can I do this using a simple SQL query?

    Thanks in advance.

    AD

-- 
www.adrianocastro.net