[Plugin: Exports and Reports] Help joining queries

You could try this:

SELECT
post_title, meta_key, meta_value
FROM wp_posts
LEFT JOIN wp_postmeta ON post_id = wp_posts.ID AND meta_key IN ('regno_value', 'price_value', 'color_value', 'year_value')
WHERE wp_posts.post_type = 'listing'

Thank you for getting back to me so quickly. That’s ALMOST it…

The only issue is it displays each meta key on a separate row – so each van is displayed 4 times (screenshot: https://twitpic.com/6rbdg8/full )

is there anyway to get it so that it displays the data in the same row?
eg:
post_title | reg_no_value | price_value | color_value | year_value

thanks

SELECT
p.post_title,
m1.meta_value AS regno_value,
m2.meta_value AS price_value,
m3.meta_value AS color_value,
m4.meta_value AS year_value,
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = p.ID AND m1.meta_key = 'regno_value'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = p.ID AND m2.meta_key = 'price_value'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = p.ID AND m3.meta_key = 'color_value'
LEFT JOIN wp_postmeta AS m4 ON m4.post_id = p.ID AND m4.meta_key = 'year_value'
WHERE p.post_type = 'listing'

Thank you again – unfortunately that doesn’t appear to work just returns a ‘No items found’ message

I’ve managed to get the query working.

However although this works in the mySQL dashboard it doesn’t work in the plugin.

Is there anything else I need to/can do to make this work in the plugin?

SET SESSION SQL_BIG_SELECTS=1;
SELECT
post_title,
regno.meta_value as regno,
price.meta_value as price,
color.meta_value as color,
year.meta_value as year,
mileage.meta_value as mileage,
enginesize.meta_value as enginesize,
doors.meta_value as doors,
trans.meta_value as trans,
fueltype.meta_value as fueltype,
owners.meta_value as owners,
body_type.meta_value as body_type
FROM wp_posts
LEFT JOIN wp_postmeta AS regno ON regno.post_id=ID AND regno.meta_key='regno_value'
LEFT JOIN wp_postmeta AS price ON price.post_id=ID AND price.meta_key='price_value'
LEFT JOIN wp_postmeta AS color ON color.post_id=ID AND color.meta_key='color_value'
LEFT JOIN wp_postmeta AS year ON year.post_id=ID AND year.meta_key='year_value'
LEFT JOIN wp_postmeta AS mileage ON mileage.post_id=ID AND mileage.meta_key='mileage_value'
LEFT JOIN wp_postmeta AS enginesize ON enginesize.post_id=ID AND enginesize.meta_key='enginesize_value'
LEFT JOIN wp_postmeta AS doors ON doors.post_id=ID AND doors.meta_key='doors_value'
LEFT JOIN wp_postmeta AS trans ON trans.post_id=ID AND trans.meta_key='trans_value'
LEFT JOIN wp_postmeta AS fueltype ON fueltype.post_id=ID AND fueltype.meta_key='fueltype_value'
LEFT JOIN wp_postmeta AS owners ON owners.post_id=ID AND owners.meta_key='owners_value'
LEFT JOIN wp_postmeta AS body_type ON body_type.post_id=ID AND body_type.meta_key='body_type_value'
WHERE wp_posts.post_type = 'listing'
AND post_status = 'publish'
ORDER BY
ID

You could configure your MySQL.ini settings to allow big selects, otherwise I’d have to make some changes to this plugin to allow for that. The way it runs it’s queries is specific and it does rewriting on the query to handle pagination / filtering too, so there’s a lot in the mix.