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.
Related:
Posted January 16, 2008 by Casey
Categories: Technology. Tags: join, mysql, queries, subselect, syntax.
1 Comment(s)
Comments RSS
TrackBack Identifier URI
Leave a comment
User contributed tags for this post:
MySQL query syntax (56) - time passing quote (16) - mysql photo syntax (15) - mysql syntax (13) - mysql conditional select (9) - mysql conditional join (8) - mysql search syntax (8) - MYSQL SYNTAX SELECT (8) - mysql union syntax (6) - select query with condition (6) - mysql or syntax (5) - mysql multiple joins (5) - WWW XXL COM (5) - and syntax in query mysql (5) - mysql multiple conditions (5) - writing mysql queries (4) - mysql query comment (4) - mysql subselect syntax (4) - mysql join syntax (4) - mysql where syntax (4) - mysql conditionals (3) - syntaxe mysql (3) - mysql subselect (3) - mysql syntax join (3) - query 1=1 (3) - mysql query conditional (3) - mysql select where syntax (3) - mysql syntax where condition (3) - php mysql union (3) - conditional mysql query (3) - mysql conditional syntax (3) - mysql query syntax reference (3) - mysql where 1=1 (3) - mysql join multiple conditions (3) - mysql where condition syntax (3) - mysql query by week (3) - mysql queries syntax (3) - mysql conditional union (3) - mysql select last syntax (2) - sql - selects and subselects efficiency (2) - conditional join mysql (2) - mysql conditional query (2) - Query Syntax in mySQL (2) - left join mysql syntax (2) - mysql query 2 joins (2) - mySQL multiple conditionals (2) - wordpress mysql query (2) - mysql syntaxe select (2) - mysql multiple select (2) - php mysql subselect (2) - mysql query with if condition (2) - mysql query conditionals (2) - mysql conditional update (2) - syntax mysql (2) - mysql query syntax comment (2) - mysql multiple condition (2) - mysql php query syntax with if statements (2) - select mysql syntax (2) - mysql conditional update query (2) - mysql union and join query (2) - mysql multiple select query (2) - mysql syntax where 1 (2) - mysql query last (2) - multiple join syntax mysql (2) - conditions in mysql queries php (2) - mysql tag cloud sub select (2) - conditional statements in mysql query (2) - mysql conditional queries (2) - mysql where multiple conditions (2) - mysql syntax multiple joins (2) - mysql query wordpress (2) - if condition in mysql select query (2) - mysql query comment syntax (2) - mysql multiple update syntax (2) - mysql select * where and (2) - Select query syntax (2) - mysql left join on syntax (2) - MULTIPLE LEFT JOIN IN MYSQL QUERY (2) - php mysql union syntax (2) - mysql - join multiple (2) - mysql syntax select item and one picture (2) - mysql query syntax left join (2) - mysql multiple search syntax (2) - first (2) - mysql syntax wiki (2) - MySQL multiple Join syntax (2) - mysql querry syntax (2) - mysql syntax where (2) - mysql tag query (2) - mysql querry (2) - mysql multiple where condition (2) - mysql syntax email (2) - mysql union vs left join (2) - mysql select query syntax (2) - mysql syntax query (2) - mysql query condition (2) - php mysql multiple update syntax (2) - mysql as syntax (2) - mysql query comments (2) - mysql multiple join statement (2) - mysql query where 1=1 (2) - multiple join syntax (2) - mysql query multiple conditions (2) - mysql join multiple ids (1) - week left join sql (1) - mysql join query (1) - join query using syntax in mysql (1) - mysql query multiconditional (1) - 2 joins in 1 query mysql (1) - mysql 1 condition (1) - multiple UNION queries in MySQL (1) - writing queries in mysql (1) - sql multiple union (1) - mysql join statement having multiple ON conditions (1) - mysql syntax mail'@'% (1) - MULTIPLE SELECT query syntax (1) - querry in mysql (1) - left join 5 syntax subselect (1) - mysql join syntax for multiple columns (1) - mysql in condition syntax (1) - mysql where condition in (1) - update query in Mysql (1) - mysql query multiselect results (1) - mysql multi where condition (1) - mysql query if condition syntax (1) - MYSQL multiple WHERE OR CLAUSE (1) - mysql if condition in select query (1) - multiple left joins mysql syntax (1) - mysql syntax comment (1) - sql syntax update query multiple condition (1) - mysql multiple ON conditions for join (1) - mysql syntax and query (1) - multiple conditions in update query mysql (1) - mysql 2 condition (1) - mysql multiple select statements in a query (1) - php mysql WHERE 2 conditions (1) - mysql join select syntax (1) - mysql select where 2 conditions (1) - mysql query with multiple conditions between (1) - update query with multiple conditions in mysql (1) - WHERe MySQL syntaxe (1) - select query and where condition in mysql (1) - update query syntax with example for mysql (1) - select one from multiple sources mysql (1) - mysql update multiple condition (1) - multiple select query for mysql (1) - mysql hack query (1) - mysql conditional Select Query Statements (1) - mysql database query syntax search (1) - conditional statement inside mysql query (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.