[thelist] SQL: ORDER with two parameters
Tab Alleman
Tab.Alleman at MetroGuide.com
Tue Mar 30 15:36:02 CST 2004
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
?
More information about the thelist
mailing list