Joining two tables where one has multiple rows

Question

I am trying to convert Wordpress data into my own database and their database structure is giving me issues. This is difficult to explain so bear with me.

I have two tables as followed. They are joined by wp_post.ID = wp_postmeta.post_id.

wp_posts
+----+------------+--------------+
| ID | post_title | post_content |
+----+------------+--------------+
| 1  | Game Title | some content |
+----+------------+--------------+

wp_postmeta
+---------+--------------+------------+
| post_id | meta_key     | meta_value |
+---------+--------------+------------+
|    1    | post_type    | Review     |
+---------+--------------+------------+
|    1    | game_rating  | 8.0        |
+---------+--------------+------------+
|    1    | game_genre   | FPS        |
+---------+--------------+------------+

Right now I have to do a query for every single meta_value entry I want (WHERE meta_value = 'Review') for example.

Is there a way for me to run a single query and return post_title, post_content, post_type, gaming_rating, and game_genre?

score 3 · Accepted Answer · answered Mar 03 '12 at 06:13

Looks like you need to do a couple of self joins on the meta table.

;with cte as(
  select pt.post_id, gr.meta_value as game_rating,
         gg.meta_value as game_genre
  from wp_postmeta pt
    inner join wp_postmeta gr
       on pt.post_id= gr.post_id and 
           gr.meta_key= 'game_rating'
    inner join wp_postmeta gg
   on pt.post_id = gg.post_id and 
       gg.meta_key = 'game_genre'
 where pt.meta_key ='post_type' and 
       pt.meta_value ='review'
 )
 select p.post_title, post_content,
        c.game_rating, c.game_genre
 from wp_post p
     inner join cte c
        on c.post_id = p.id

see this fiddle http://sqlfiddle.com/#!3/e7559 to see it in action.

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

This is a kind of pivot table; see the answer to this question for information on how to do this in MySQL.

edited May 23 '17 at 11:55

Community

1
1

answered Mar 02 '12 at 22:42

Ken Keenan

9,818
5
32
49

Juan Enrique Muñoz Zolotoochin · Answer 3 · 2012-03-02T22:56:22.820

You could do a regular inner join

SELECT post_id, post_title, post_content, meta_key, meta_value FROM wp_posts P INNER JOIN wp_postmeta PM ON (P.ID = PM.post_id) ORDER BY post_id

And then use a simple script to iterate thru the rows of each post. I don't know if you are using any language to access the DB, but in python (which is like pseudo code :P) it would be something like:

post = {}

for row in result:
   first_iteration = 'id' not in post

   if not first_iteration and row['post_id'] != post['id']:
      # we have a new post, process it.

   post['post_title'] = row['post_title']
   post['post_content'] = row['post_content']
   post[row[meta_key]] = row['meta_key']
   post['id'] = row['id']


 # process the last post

Joining two tables where one has multiple rows

3 Answers3