Q:

Get data from 3rd table

I got 3 tabels:

wp_posts
wp_metadata 
users

When I get my query from "wp_posts" i'm using LEFT JOIN to get the data from the "wp_metadata", but how can I get a value from the 3rd table "users"?
Example:
I have one post on my wordpressblog. On that post I have some meta-fields - one of them is called "userid". My users is located in a table outside of wordpress. How can I get the users username from the table "users" where the post-meta-key "userid" is equal to the "users"-tables row ID?
Here is my SQL-request as it is right now:
SELECT ID, post_title,
    post_excerpt, 
    post_content, 
    m.meta_value AS placeid, 
    i.meta_value AS photoid, 
    r.meta_value AS rating, 
    u.meta_value AS userid, 
    b.username 
FROM wp_posts p 
LEFT JOIN wp_postmeta m ON (m.post_id = p.ID) 
LEFT JOIN wp_postmeta i ON (i.post_id = p.ID) 
LEFT JOIN wp_postmeta r ON (r.post_id = p.ID) 
JOIN wp_postmeta u ON (u.post_id = p.ID) 
LEFT JOIN brugere b ON (b.id = userid) 
WHERE m.meta_key = 'placeid' 
AND i.meta_key = 'photoid' 
AND r.meta_key = 'rating' 
AND u.meta_key = 'userid' 
AND post_status = 'publish' 
ORDER BY ID DESC LIMIT 50;

I want ONE query, because i'm using the query on a "print json_encode". 

A:

SELECT p.* 
     , x.placeid
     , x.photoid
     , x.userid
     , x.rating
     , u.username
  FROM wp_posts p
  LEFT
  JOIN 
     ( SELECT post_id
            , MAX(CASE WHEN meta_key = 'placeid' THEN meta_value END) placeid
            , MAX(CASE WHEN meta_key = 'photoid'  THEN meta_value END) photoid
            , MAX(CASE WHEN meta_key = 'userid'  THEN meta_value END) userid
            , MAX(CASE WHEN meta_key = 'rating'  THEN meta_value END) rating
         FROM wp_postmeta
        GROUP
           BY post_id
     ) x 
    ON x.post_id = p.id
  LEFT
  JOIN brugere u
    ON u.id = x.userid;

+----+--------------+--------------+----------------+-------------+-----------+---------+---------+--------+--------+----------+
| ID | post_content | post_title   | post_excerpt   | post_status | post_type | placeid | photoid | userid | rating | username |
+----+--------------+--------------+----------------+-------------+-----------+---------+---------+--------+--------+----------+
|  1 | Content 1    | Post title 1 | Post ecxerpt 1 | publish     | post      | 101     | 102     | 2      | 4      | Bruger 2 |
|  2 | Content 2    | Post title 2 | Post ecxerpt 2 | publish     | post      | 201     | 202     | 3      | 3      | Bruger 3 |
|  3 | Content 3    | Post title 3 | Post ecxerpt 3 | publish     | post      | 301     | 302     | 3      | 2      | Bruger 3 |
|  4 | Content 4    | Post title 4 | Post ecxerpt 4 | publish     | post      | NULL    | NULL    | NULL   | NULL   | NULL     |
|  5 | Content 5    | Post title 5 | Post ecxerpt 5 | publish     | post      | NULL    | NULL    | NULL   | NULL   | NULL     |
+----+--------------+--------------+----------------+-------------+-----------+---------+---------+--------+--------+----------+

http://sqlfiddle.com/#!2/f517a/10

