Join two tables, in which second table needs to be joined many times

Question

Hi please see my two table and data

 /* wp_posts */

 ID    post_title         post_type   post_status
 1     testproduct        product     publish

 /* wp_postmeta */

 meta_id   post_id   meta_key        meta_value
 1           1       _price           20
 2           1       _regularprice    30
 3           1       _stock_status    instock
 4           1        _qty             3

I need to fetch all data from these two table for getting every details of product . For that I write the following query, but it doesn't work, please help.

SELECT p.post_title,m.meta_key,m.meta_value FROM wp_posts p LEFT  JOIN wp_postmeta m LEFT JOIN wp_post_meta m1 ON p.ID = m.post_id AND m.meta_key = '_price' AND m1.meta_key= '_regular_price' WHERE p.post_status = 'publish' AND p.post_type = 'product'

Each LEFT JOIN needs to have it's `ON` specified, you only have `ON` for the second LEFT JOIN. — apokryfos, Apr 11 '16 at 14:42
You have both `wp_postmeta` and `wp_post_meta` tables? seems strange. — sagi, Apr 11 '16 at 14:44

score 1 · Accepted Answer · answered Apr 11 '16 at 14:46

1

Looks like you write too complex query. Try to simplify. Like this

select p.post_title,m.meta_key,m.meta_value 
from wp_post p 
join wp_postmeta m on p.id=m.post_id 
where p.post_status = 'publish' AND p.post_type = 'product' and
(m.meta_key='_price' or m.meta_key='_regular_price')

answered Apr 11 '16 at 14:46

Vasyl Moskalov

4,242
3
20
28

Could you please check this question also http://stackoverflow.com/questions/36526268/sql-query-to-download-order-report-in-woocommerce . Please help . – Manik Apr 11 '16 at 14:51

Join two tables, in which second table needs to be joined many times

1 Answers1