I have two tables:

 _____________________________
| wp_post      | wp_postmeta |
|______________|_____________|
| ID           | meta_id     |
| post_title   | post_id     |
| post_content | meta_key    |
| guid         | meta_value  |
|______________|_____________|

The wp_postmeta table contains these rows:

| meta_id | post_id | meta_key        | meta_value
|---------|---------|-----------------|---------------
| 310     | 156     | level           | Blue
| 311     | 156     |_post_main_intro | Some text

The result I want is:

post_title, post_content, meta_value as color, meta_value as main_intro

I've tried different joins, but I'm not quite able to fix it.

This is as close as I get:

SELECT a.post_title, b.meta_key, b.meta_value
FROM wp_posts a
LEFT JOIN wp_postmeta b ON a.ID = b.post_id
WHERE b.meta_key = 'level'

But this does not allow me to fetch _post_main_intro data.

I'll be very happy if someone can push me in the right direction :)

Update
My current solution is this (and it works)

SELECT a.id, a.post_title, b.meta_value as color, c.meta_value as post_intro
FROM wp_posts a
LEFT JOIN wp_postmeta b ON a.ID = b.post_id
LEFT JOIN wp_postmeta c ON a.ID = c.post_id
WHERE b.meta_key = 'level'
AND  c.meta_key = '_post_main_intro'

Is this thew way to go?

You will need the meta table twice in your query:

SELECT a.post_title, b.meta_key, b.meta_value as level, c.meta_value as intro
FROM wp_posts a
LEFT JOIN wp_postmeta b ON a.ID = b.post_id
LEFT JOIN wp_postmeta c ON a.ID = c.post_id
WHERE b.meta_key = 'level'
AND c.meta_key = '_post_main_intro'

Edit: forgot to include third table in columns

more