Page 1 of 1

Problems ordering my query using left join...

Posted: Mon Aug 01, 2005 3:56 am
by icarpenter
Hi can any help me order a query...

When I run:-
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
The result returned is:-

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          | 
+----------+----------+----------+----------+----------+
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.

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         |
+----------+----------+----------+----------+----------+
Would anyone know of a way that I can achieve this? I will be greatfull for any advice...

Thanks Ian

Posted: Mon Aug 01, 2005 5:15 am
by timvw

Code: Select all

SELECT t1.id, t2.id,t2.column1,t2.column2, (t2.column1+t2.column2) as total
FROM table1 AS t1 
LEFT JOIN table2 AS t2 USING (id)
ORDER BY IFNULL(total, 0) DESC

Posted: Mon Aug 01, 2005 7:23 am
by icarpenter
Hi timvw

Thanks for that it is moving in the right direction but doent seem to total column in the line 'ORDER BY IFNULL(total, 0) DESC'

This is my actual query:

SELECT
t1.dbsrvcoid,t1.dbsrvconame,t1.dbsrvcotrade,t1.dbsrvcotradecat,t1.dbsrvcotext1,t1.dbsrvcotext2,
t3.dbpostcode,t3.dbsrvcoid,
t4.dbpostcode,t4.dbgeoxy_lat,t4.dbgeoxy_lon,
(t4.dbgeoxy_lat+t4.dbgeoxy_lon) as distance

FROM dbsrvco AS t1,dbuser AS t3 LEFT JOIN dbgeo_xy AS t4 USING (dbpostcode)
WHERE t1.dbsrvcoid=t3.dbsrvcoid

ORDER BY IFNULL(distance,0) DESC

Is ther anything obvious that I am missing? I am usin MtySQL version 4.1

Thanks Ian

Posted: Mon Aug 01, 2005 7:28 am
by icarpenter
Sorry thay should have read...

Thanks for that it is moving in the right direction but it doesn't seem to recognise the total column in the line 'ORDER BY IFNULL(total, 0) DESC'

Posted: Mon Aug 01, 2005 7:34 am
by timvw
You could try something like:

Code: Select all

SELECT
t1.dbsrvcoid,t1.dbsrvconame,t1.dbsrvcotrade,t1.dbsrvcotradecat,t1.dbsrvcotext1,t1.dbsrvcotext2,
t3.dbpostcode,t3.dbsrvcoid,
t4.dbpostcode,t4.dbgeoxy_lat,t4.dbgeoxy_lon,
(t4.dbgeoxy_lat+t4.dbgeoxy_lon) as distance,
IFNULL(distance, 0) AS order

FROM dbsrvco AS t1,dbuser AS t3 LEFT JOIN dbgeo_xy AS t4 USING (dbpostcode)
WHERE t1.dbsrvcoid=t3.dbsrvcoid

ORDER BY order DESC

Posted: Mon Aug 01, 2005 7:39 am
by icarpenter
I am getting the error unknown column 'distance' in 'field list' - 1054

it's like it doesn't recognise anything within the IFNULL() statment. it's the same error as before...

Ian.

Posted: Mon Aug 01, 2005 7:48 am
by timvw

Code: Select all

SELECT
t1.dbsrvcoid,t1.dbsrvconame,t1.dbsrvcotrade,t1.dbsrvcotradecat,t1.dbsrvcotext1,t1.dbsrvcotext2,
t3.dbpostcode,t3.dbsrvcoid,
t4.dbpostcode,t4.dbgeoxy_lat,t4.dbgeoxy_lon,
IFNULL(t4.dbgeoxy_lat+t4.dbgeoxy_lon, 0) as distance,

FROM dbsrvco AS t1,dbuser AS t3 LEFT JOIN dbgeo_xy AS t4 USING (dbpostcode)
WHERE t1.dbsrvcoid=t3.dbsrvcoid

ORDER BY distance DESC
This would give you a 0 distance instead of NULL...

Posted: Mon Aug 01, 2005 7:59 am
by icarpenter
I think this should be ascending as i am getting distances 0,0,1,2,3,4 instead of 1,2,3,4,0,0 or 1,2,3,4,Null,Null(which is what i am aming for)

in the previous querys The IFNULL() statement works on every other column exept the distance column...

The last method does work if the value is set to 999999 as it pulls out the distances as 1,2,3,4,999999,999999 but somehow I need these to be null or 0!

Ian.

Posted: Mon Aug 01, 2005 10:14 am
by icarpenter
timvw I have got the results required using the last method...

Thanks for your help...

Rgds Ian