Ordering by a field, only if it exists

Question

I'm trying to get all users, and order them by a field on another table, however this field doesn't always exist?

Users - Holds Users User Meta - Holds metadata, specifically "weight" which is what I'm trying to order by.

A more concrete solution would be to automatically define them a default weight, however is there anyway I can make it work without? Current working query:

SELECT * FROM users u, usermeta um 
WHERE u.ID = um.ID 
  AND u.name LIKE '%search_terms%';

Lost on the order by part, any/all help would be appreciated!

you can use this : `order by IFNULL(um.weight,0)` – rs. Mar 06 '12 at 17:37 — rs., Mar 06 '12 at 17:37

score 5 · Accepted Answer · edited Oct 14 '13 at 11:47

5

If you have a key relationship between the tables (u.ID = um.ID) and you want to list all users, you could use something like this to order by weight on usermeta.

The LEFT JOIN lets you keep ALL users, regardless of their existence in usermeta:

SELECT *, IFNULL(um.weight,9999) as newweight
FROM users u
LEFT JOIN usermeta um on u.ID = um.ID
WHERE u.name LIKE '%search_terms%'
ORDER BY IFNULL(um.weight,9999);

edited Oct 14 '13 at 11:47

Urbycoz

7,247
20
70
108

answered Mar 06 '12 at 17:39

Vinnie

3,889
1
26
29

If your field is `NOT NULL`, but you want to do the same thing with zero instead of NULL, you can replace `IFNULL(um.weight,9999)` with `IF(um.weight, um.weight, 9999)` – jmorganmartin Apr 11 '18 at 22:20

score 1 · Answer 2 · answered Mar 06 '12 at 17:35

1

You can use MySQL's IFNULL to define a default value for weight

See: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

answered Mar 06 '12 at 17:35

sikander

2,286
16
23

score 1 · Answer 3 · answered Mar 06 '12 at 17:37

SELECT * 
FROM users u, usermeta um 
WHERE u.ID = um.ID AND                                                                                                                                                                                                                       
      u.name LIKE '%search_terms%'
ORDER BY  
      CASE WHEN um.weight IS NULL THEN 9999 ELSE um.weight END;

You can use a case statement to react to a missing weight by substituting it for something else if it is missing.

Ordering by a field, only if it exists

3 Answers3

Linked