CODEWITHSUNDEEP
mysqlsql
user652650
user652650

Reputation: 638

Ordering by a field, only if it exists

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!

Upvotes: 3

Views: 4275

Answers (3)

Vinnie
Vinnie

Reputation: 3929

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);

Upvotes: 5

Ryan
Ryan

Reputation: 28227

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.

Upvotes: 1

sikander
sikander

Reputation: 2286

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

Upvotes: 1

Related Questions