Problems ordering my query using left join...
Posted: Mon Aug 01, 2005 3:56 am
Hi can any help me order a query...
When I run:-
Which is correct however where the ORDER BY is ASC it puts all of the entries in the total column with the value NULL in front of the actual data that that I would like to read first ie: 11,33,44...
I would like to order it so it returns the total as 11,33,44,Null,Null,Null.
Would anyone know of a way that I can achieve this? I will be greatfull for any advice...
Thanks Ian
When I run:-
The result returned is:-SELECT
t1.id,
t2.id,t2.column1,t2.column2,
(t2.column1+t2.column2) as total
FROM table1 AS t1 LEFT JOIN table2 AS t2
ON t1.id=t2.id
ORDER BY total desc
Code: Select all
+----------+----------+----------+----------+----------+
| id(t1) | id(t2) | Column1 | Column2 | total |
+----------+----------+----------+----------+----------+
| 1 | 1 | 22 | 22 | Null |
| 1 | 1 | 11 | 22 | Null |
| 1 | 1 | 22 | 22 | Null |
| 1 | 1 | 11 | 11 | 11 |
| 2 | 1 | 11 | 22 | 33 |
| 1 | 1 | 22 | 22 | 44 |
+----------+----------+----------+----------+----------+I would like to order it so it returns the total as 11,33,44,Null,Null,Null.
Code: Select all
+----------+----------+----------+----------+----------+
| id(t1) | id(t2) | Column1 | Column2 | total |
+----------+----------+----------+----------+----------+
| 1 | 1 | 11 | 11 | 11 |
| 2 | 1 | 11 | 22 | 33 |
| 1 | 1 | 22 | 22 | 44 |
| 1 | 1 | 22 | 22 | Null |
| 1 | 1 | 11 | 22 | Null |
| 1 | 1 | 22 | 22 | Null |
+----------+----------+----------+----------+----------+Thanks Ian