Bits Of MySQL Query Syntax I’ve Learned This Week

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.

2 thoughts on “Bits Of MySQL Query Syntax I’ve Learned This Week

  1. This is great stuff – some huge joins have been killing me and I’m glad to know I can experiment where some of those conditional statements go.

Comments are closed.