CODEWITHSUNDEEP
mysqlwordpress
Steven
Steven

Reputation: 19425

How do I fetch data from two tables as one row

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?

Upvotes: 1

Views: 356

Answers (3)

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

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'

Upvotes: 0

Stephan B
Stephan B

Reputation: 3701

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

Upvotes: 2

RoboTamer
RoboTamer

Reputation: 3518

That's because you have restricted the search to 'level'

WHERE b.meta_key = 'level'

Just remove that part, and it should give you all

Upvotes: 0

Related Questions