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
LEFT JOIN lets you keep ALL users, regardless of their existence in
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
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.