Watching the WordPress hacker list this week, a couple messages related to selecting information about users schooled me on MySQL syntax. I obviously knew the following would work, but I’d previously used the UNION syntax in similar situations and somehow hadn’t thought of writing it this way:
| ``` SELECT (SELECT meta_value FROM wp_usermeta WHERE meta_key = 'first_name' AND user_id = 2) AS FIRST, (SELECT meta_value FROM wp_usermeta WHERE meta_key = 'last_name' AND user_id = 2) AS LAST, wp_users.* FROM wp_users WHERE wp_users.ID = 2 ``` |
That’s much cleaner to my thinking, though I’ve no idea which is more optimal. When somebody replied asking for a solution that would work in pre-MySQL 5, this was the response:
| ``` SELECT ID, user_login, FIRST.meta_value AS fname, LAST.meta_value AS lname FROM wp_users LEFT JOIN wp_usermeta AS FIRST ON (wp_users.ID = FIRST.user_id AND FIRST.meta_key = 'first_name') LEFT JOIN wp_usermeta AS LAST ON (wp_users.ID = LAST.user_id AND LAST.meta_key = 'last_name') ``` |
And the lesson to me here is that I didn’t realize the syntax allowed us to match multiple conditions for the JOIN. Makes sense, but I just hadn’t thought of it. Thanks go to Phil Williams and Otto for tipping me to these.