Problems ordering my query using left join...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Problems ordering my query using left join...

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Post 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
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Post 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'
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Post 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.
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Post by icarpenter »

timvw I have got the results required using the last method...

Thanks for your help...

Rgds Ian
Post Reply