MySQL combining some data from 2 tables

1
votes

I'm working on a WordPress website using Advanced Custom Fields, if that helps at all.

I have two tables: posts and postmeta

Posts

|-------------------------------------------|
|   ID    |   post_title    |   post_type   |
|    1    |     Title 1     |       book    |
|    2    |     Title 1     |      product  |
|    3    |     Title 2     |       book    |
|    4    |     Title 2     |      product  |
|    5    |     Title 3     |       book    |
|    6    |     Title 3     |      product  |
|-------------------------------------------|

Post Meta

|----------------------------------------------------------|
|   post_id       |   meta_key      |      meta_value      |
|        3        |     Rating      |            *****     |
|        3        |    Publisher    |     Julian Celestro  |
|        3        |       Date      |     April 16, 1840   |
|        3        |       Type      |          Hardback    |
|        3        |       Price     |          19.99       |
|        3        |       ISBN      |       1234567890     |
|----------------------------------------------------------|

Of course there is a bunch of other stuff happening, but these are the relevant fields.

So in the posts table, there are 3 books, each with a book id# and a product id#.

The postmeta table contains the information about all of these books and products, by associating a meta key and value with a post id.

There are many many books, and I need a way to basically combine the two tables. For each post_title(book title) from table 1, I want the ID number that is associated with the post_type of product (id = 2,4,6) and I want to join that with the postmeta table.

So for example, I would like to have a MySQL statement that results in returning:

ID=4, post_title='Title 2', meta_key=rating, meta_value=*****
ID=4, post_title='Title 2', meta_key=Publisher, meta_value=Julian Celestro
ID=4, post_title='Title 2', meta_key=Date, meta_value=April 16, 1840
ID=4, post_title='Title 2', meta_key=Type, meta_value=Hardback
ID=4, post_title='Title 2', meta_key=Price, meta_value=19.99
ID=4, post_title='Title 2', meta_key=ISBN, meta_value=1234567890

I tried something along the lines of 

SELECT `postmeta`.*
FROM `posts` 
RIGHT JOIN `postmeta` ON posts.id = postmeta.post_id
WHERE `post_type` = 'book'

and it is (expectedly) giving me the meta_key and meta_value that I want, but it gives me the post_id of the book instead of the product.

2
mysqlsqlwordpress

2 Answers

0
votes

Add another meta_key 'product_id' setting the meta_value as the ID of the product, and the post_id as the ID of the book.

then query that :)

0
votes

I think you mean something like this

SELECT p.ID, p.post_title, pm.meta_key, pm.meta_value
FROM `posts` AS p 
RIGHT JOIN `postmeta` AS pm ON p.id = pm.post_id
WHERE `post_type` = 'book'

Output

ID  post_title  meta_key    meta_value
3   Title 2     Rating      *****
3   Title 2     Publisher   Julian Celestro
3   Title 2     Date        April 16, 1840
3   Title 2     Type        Hardback
3   Title 2     Price       19.99
3   Title 2     ISBN        1234567890