CODEWITHSUNDEEP
mysqljoin
Doug Cassidy
Doug Cassidy

Reputation: 1905

Joins with 2 values per table AS alias values

SELECT
`wp_posts`.`post_title`
, `wp_posts`.`post_content`
, `wp_posts`.`post_type`
, `wp_posts`.`ID`
, `wp_posts`.`post_date`
, `wp_usermeta`.`user_id` AS `userID`
, `wp_usermeta`.`meta_value` AS `city`
, `wp_usermeta`.`meta_value` AS `state`
, `wp_users`.`fbconnect_userid` AS `fbuid`
FROM    `wp_posts`    
INNER JOIN `wp_emu_shop_reviews`         
    ON (`wp_posts`.`ID` = `wp_emu_shop_reviews`.`postID`)
LEFT JOIN `wp_users`                
    ON (`wp_emu_shop_reviews`.`wpUserID` = `wp_users`.`ID`)
LEFT JOIN `wp_usermeta`              
    ON (`wp_users`.`ID` = `wp_usermeta`.`user_id` AND `wp_usermeta`.`meta_key` ='location_city')
        OR(`wp_users`.`ID` = `wp_usermeta`.`user_id` AND `wp_usermeta`.`meta_key` ='location_state')
WHERE (`wp_posts`.`post_type` = "review"
AND `wp_posts`.`post_status` = "publish");

The part of this that doesnt work is getting the city and state into the results. The current query puts the city into both city and state column. Obv, the last join is the problem.

"This is the title" "blahblahblah"  "review"    "2074"  "2012-05-03 00:03:10"   "15"    "Redmond"   "Redmond"   "xxxxxxxx"

I probly need to get more values out of the the wp_usermeta table, but I'm sure I can figure it out if someone soves this question for me.

Ah.. On advice from Juris Malinens and Explosion Pills I got this query working, I think. 

SELECT
`wp_posts`.`post_title`
/* , `wp_posts`.`post_content` */
, `wp_posts`.`post_type`
, `wp_posts`.`ID`
, `wp_posts`.`post_date`
/* , `wp_usermeta`.`user_id` AS `userID` */
, um1.user_id AS `userID` 
, um1.meta_value AS `city`
, um2.meta_value AS `state`
, `wp_users`.`fbconnect_userid` AS `fbuid`
FROM    `wp_posts`    
INNER JOIN `wp_emu_shop_reviews`         
ON (`wp_posts`.`ID` = `wp_emu_shop_reviews`.`postID`)
LEFT JOIN `wp_users`                
ON (`wp_emu_shop_reviews`.`wpUserID` = `wp_users`.`ID`)
/*  LEFT JOIN `wp_usermeta`              
ON (`wp_users`.`ID` = `wp_usermeta`.`user_id` AND `wp_usermeta`.`meta_key` ='location_city') */
LEFT JOIN wp_usermeta um1 ON (`wp_users`.`ID` = `um1`.`user_id` AND um1.meta_key ='location_city') 
LEFT JOIN wp_usermeta um2 ON(`wp_users`.`ID` = `um2`.`user_id` AND um2.meta_key ='location_state')

WHERE (`wp_posts`.`post_type` = "review"
AND `wp_posts`.`post_status` = "publish");

Upvotes: 0

Views: 143

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191749

The query has no way of knowing which of the meta values you want to join on for particular rows, and in fact it's impossible in the current query (i.e. it can only join on one at a time). You need to join on wp_usermeta twice:

SELECT
    state.user_id AS userID
    state.meta_value AS state,
    city.meta_value AS city,
FROM
    wp_users
    JOIN wp_usermeta state ON (wp_users.ID = state.user_id
       AND state.meta_key = 'location_state')
    JOIN wp_usermeta city ON (wp_users.ID = city.user_id
       AND state.meta_key = 'location_city')

That's the gist of what you need to do; just add in your other joins and conditions.

Upvotes: 2

Related Questions