How do you fetch data from two tables as you row

I've two tables:

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

The wp_postmeta table consists of these rows:

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

The end result I would like is:

post_title, post_content, meta_value as color, meta_value as main_intro

I have attempted different joins, but I am less than in a position to repair it.

This is because close when 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 doesn't let me fetch _post_main_intro data.

I'm going to be happy if a person can push me within the right direction :)

Update
My current option would be this (and delay pills work)

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 thew approach to take?

mysql wordpress

Try:



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' OR b.meta_key = '_post_main_intro'

Like you've restricted the search to 'level'

WHERE b.meta_key = 'level'

Just remove that part, also it should provide you with all

You'll need the meta table two times inside 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: didn't remember to incorporate third table in posts

Related