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.
Posted January 16, 2008 by Casey Bisson
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 (70) - time passing quote (16) - mysql syntax (15) - mysql photo syntax (15) - MYSQL SYNTAX SELECT (12) - mysql conditional join (12) - mysql conditional select (12) - mysql conditionals (10) - mysql search syntax (8) - mysql update subselect (8) - mysql query comment (7) - mysql union syntax (7) - mysql where syntax (7) - mysql join syntax (7) - mysql multiple conditions (6) - syntaxe mysql (6) - select query with condition (6) - mysql multiple joins (5) - mysql conditional update (5) - mysql conditional syntax (5) - mysql or syntax (5) - mysql subselect (5) - mysql subselect syntax (5) - and syntax in query mysql (5) - WWW XXL COM (5) - mysql query by week (4) - mysql as syntax (4) - mysql select where syntax (4) - mysql query comments (4) - mysql query multiple conditions (4) - if condition in mysql query (4) - mysql query where 1=1 (4) - select * where and (4) - mysql where 1=1 (4) - mysql conditional union (4) - mysql syntax where condition (4) - writing mysql queries (4) - mysql where condition syntax (4) - mysql where multiple conditions (4) - mysql conditional query (4) - mysql multiple select query (4) - mysql queries syntax (4) - mysql query conditionals (3) - mysql syntax join (3) - mysql conditional joins (3) - mysql syntax where (3) - mysql query comment syntax (3) - php mysql union syntax (3) - php mysql subselect (3) - mysql query conditional (3) - multiple select join syntax in mysql (3) - mysql conditional subselect (3) - mysql query where syntax ? (3) - conditional mysql query (3) - mysql query syntax reference (3) - mysql join multiple conditions (3) - select mysql syntax (3) - query 1=1 (3) - users (3) - where condition syntax mysql (3) - mysql select * where and (3) - mysql querry (3) - Query Syntax in mySQL (3) - mysql query if condition (3) - php mysql union (3) - mysql update multiple joins (2) - conditions in mysql queries php (2) - mysql syntax multiple joins (2) - left join mysql syntax (2) - php mysql multiple update queries (2) - mysql multiple select (2) - mysql multiple query syntax (2) - select mysql multiple conditions in where statement (2) - mysql where 1 (2) - mysql union vs join (2) - mysql multiple selects (2) - mysql query wordpress (2) - mysql conditional queries (2) - Select query syntax (2) - mysql query last (2) - mysql multiple update syntax (2) - mySQL multiple conditionals (2) - MULTIPLE LEFT JOIN IN MYSQL QUERY (2) - sql - selects and subselects efficiency (2) - multiple join syntax mysql (2) - mysql left join on syntax (2) - php mysql join syntax (2) - php mysql multiple update syntax (2) - update query in Mysql (2) - mysql conditional update query (2) - conditional statements in mysql query (2) - mysql syntax select item and one picture (2) - mysql multiple where conditions (2) - mysql query condition (2) - mysql multiple where condition (2) - mysql query syntax left join (2) - mysql union vs left join (2) - Conditional query mysql (2) - conditional statement in mysql query (2) - mysql querry syntax (2) - mysql if condition in select query (2) - www.sexanimals (2) - mysql union left join (2) - MySQL multiple Join syntax (2) - select where syntax mysql (2) - syntax for union in MySQL (2) - php mysql query condition (2) - mysql query with if condition (2) - if condition in mysql select query (2) - mysql select syntax (2) - 1=2 in mysql query (2) - mysql syntax where 1 (2) - syntax mysql query (2) - mysql union and join query (2) - MYSQL CONDITIONAL STATEMENTS (2) - mysql php query syntax with if statements (2) - mysql query 2 joins (2) - mysql multiple join statement (2) - syntax mysql (2) - mysql syntax wiki (2) - mysql subselect efficiency (2) - mysql query syntax comment (2) - conditional join mysql (2) - mysql syntax email (2) - mysql multiple condition (2) - wordpress mysql query (2) - mysql update syntax multiple conditions (2) - mysql select query syntax (2) - mysql syntax query (2) - mysql syntaxe select (2) - mysql select last syntax (2) - mysql tag query (2) - first (2) - mysql multiple search syntax (2) - mysql update with left join sub select (2) - query multiple JOIN syntax (2) - mysql - join multiple (2) - multiple join syntax (2) - mysql tag cloud sub select (2) - MySQL select query conditional (2) - php mysql 2 SELECT in 1 query (1) - Sql Update multiple columns with subselect (1) - mysql last syntax (1) - php subselects (1) - mysql comments in query (1) - mysql 5.0 queries syntaxes (1) - php 4 join syntax (1) - mysql two select query (1) - mysql php select where two criteria (1) - select query in mySQL with two conditions (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.