[thelist] SQL: ORDER with two parameters

[Tab Alleman]

Adriano Castro wrote:
>     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
> between 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
> 
> --

Maybe...

SELECT d1.id, d1.id_type, d1.reference
FROM doc_list d1
INNER JOIN doc_relationships dr ON d1.id=dr.id_doc 
INNER JOIN doc_list d2 ON dr.id_referrer=d2.id
WHERE d1.id_type=2
ORDER BY d2.reference, d1.reference

?