Hello all,

I'm having trouble with writing a SELECT query. It seemed to go well, but after joining a last LEFT JOIN I'm getting unexpected results.

My (and Wordpress') tables are as following:

_ wpds_postmeta _____

post_id     meta_key             meta_value

37          page_description     This is the description

_ wpds_posts _____

ID     post_title     guid

37     The title      http://example-url.com/example/

_ wpds_releases _____

release_id     post_id     region_id     platform_id     release_date         release_url                   release_info

11             37          2             3               2014-11-20 14:00     http://example.com/store/     Information about the release

_ wpds_platforms _____

platform_id     platform_name     platform_short

3               PlayStation 4     PS4

_ wpds_regions _____

region_id     region_name     region_short

2             Europe          EU
My query is the following:

GROUP_CONCAT(wpds_releases.release_date SEPARATOR "|") AS release_date,
GROUP_CONCAT(wpds_releases.release_url SEPARATOR "|") AS release_url,
GROUP_CONCAT(wpds_releases.release_info SEPARATOR "|") AS release_info,
GROUP_CONCAT(wpds_platforms.platform_name SEPARATOR "|") AS platform_name,
GROUP_CONCAT(CASE WHEN wpds_platforms.platform_short = "" THEN wpds_platforms.platform_name ELSE wpds_platforms.platform_short END SEPARATOR "|") AS platform_short,
GROUP_CONCAT(wpds_regions.region_name SEPARATOR "|") AS region_name,
GROUP_CONCAT(CASE WHEN wpds_regions.region_short = "" THEN wpds_regions.region_name ELSE wpds_regions.region_short END SEPARATOR "|") AS region_short,
wpds_posts.post_title AS post_title,
wpds_posts.guid AS post_url/*,
wpds_postmeta.meta_value AS post_description*/
FROM wpds_releases
LEFT JOIN wpds_platforms ON wpds_platforms.platform_id = wpds_releases.platform_id
LEFT JOIN wpds_regions ON wpds_regions.region_id = wpds_releases.region_id
LEFT JOIN wpds_posts ON wpds_posts.ID = wpds_releases.post_id
/*LEFT JOIN wpds_postmeta ON wpds_postmeta.post_id = wpds_posts.ID AND wpds_postmeta.meta_key = "page_description"*/
GROUP BY wpds_posts.ID
ORDER BY wpds_posts.post_title, wpds_regions.region_name, wpds_platforms.platform_name ASC
With the lines commented, there's no problem and the query runs fast and accurate. Without the comment tags however, the query returns quite some unexpected results. (Also without the "AND", which I'm not 100% sure about, in the last (commented) LEFT JOIN. With unexpected results I mean it seems like it's doing a non-distinctive GROUP_CONCAT, but without values and only the "|" seperators.

Please help me out, I'm so close to finishing this plugin (at least, I think so ).