问题描述:

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!

网友答案:

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);
网友答案:

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

网友答案:
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.

相关阅读:
Top