complex MySQL Order by not working
- by Les Reynolds
Here is the select statement I'm using. The problem happens with the sorting. When it is like below, it only sorts by t2.userdb_user_first_name, doesn't matter if I put that first or second. When I remove that, it sorts just fine by the displayorder field value pair. So I know that part is working, but somehow the combination of the two causes the first_name to override it. What I want is for the records to be sorted by displayorder first, and then first_name within that.
SELECT t1.userdb_id
FROM default_en_userdbelements as t1
INNER JOIN default_en_userdb AS t2 ON t1.userdb_id = t2.userdb_id
WHERE t1.userdbelements_field_name = 'newproject'
AND t1.userdbelements_field_value = 'no'
AND t2.userdb_user_first_name!='Default'
ORDER BY
(t1.userdbelements_field_name = 'displayorder' AND t1.userdbelements_field_value),
t2.userdb_user_first_name;
Edit: here is what I want to accomplish. I want to list the users (that are not new projects) from the userdb table, along with the details about the users that is stored in userdbelements. And I want that to be sorted first by userdbelements.displayorder, then by userdb.first_name. I hope that makes sense? Thanks for the really quick help!
Edit: Sorry for disappearing, here is some sample data
userdbelements
userdbelements_id userdbelements_field_name userdbelements_field_value userdb_id
647 heat 1
648 displayorder 1 - Sponsored 1
645 condofees 1
userdb
userdb_id userdb_user_name userdb_emailaddress userdb_user_first_name userdb_user_last_name
10 harbourlights [email protected] Harbourlights 1237 Northshore Blvd, Burlington
11 harbourview [email protected] Harbourview 415 Locust Street, Burlington
12 thebalmoral [email protected] The Balmoral 2075 & 2085 Amherst Heights Drive, Burlington
