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:
[B]_ wpds_postmeta _____[/B]
[B]
post_id meta_key meta_value
[/B]
37 page_description This is the description
[B]_ wpds_posts _____[/B]
[B]
ID post_title guid
[/B]
37 The title http://example-url.com/example/
[B]_ wpds_releases _____[/B]
[B]
release_id post_id region_id platform_id release_date release_url release_info
[/B]
11 37 2 3 2014-11-20 14:00 http://example.com/store/ Information about the release
[B]_ wpds_platforms _____[/B]
[B]
platform_id platform_name platform_short
[/B]
3 PlayStation 4 PS4
[B]_ wpds_regions _____[/B]
[B]
region_id region_name region_short
[/B]
2 Europe EU
My query is the following:
SELECT
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[COLOR="#808080"]/*,
wpds_postmeta.meta_value AS post_description*/[/COLOR]
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
[COLOR="#808080"]/*LEFT JOIN wpds_postmeta ON wpds_postmeta.post_id = wpds_posts.ID AND wpds_postmeta.meta_key = "page_description"*/[/COLOR]
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 ).
Greetings,
xtaste