Trying to get a list of unique events from wp_posts with data from wp_postmeta

I’ve spent about 4 hours trying to figure out why I cannot do a group by of the post_title and left join the date data from the wp_postmeta table

The code without the group by clause, results all my events but I only want to retrieve the first instance of the event, by grouping by the post_title, which should give me the only first date in the string of classes to sign up for.

What am I doing wrong here?

SELECT posts.post_title,meta.date
    FROM wp_posts posts
        LEFT JOIN (
            SELECT meta_value AS date,post_id FROM wp_postmeta
            WHERE wp_postmeta.meta_key ='_EventStartDate'
            ) meta
        ON posts.ID = meta.post_ID
WHERE posts.post_type='Tribe_Events' AND posts.post_status = 'publish'
GROUP BY posts.post_title

Answer

Currently meta.date is not in a GROUP BY, no math is performed on this field, and it is not part of a DISTINCT list of fields

It sounds like you want the first instance of wp_postmeta.meta_value for each wp_posts.post_title

If so, try this:

1. Ditch the subquery and resolve your meta.meta_key filter using COALESCE
2. Use the MIN function on meta.meta_value
3. If needed, CAST meta.meta_value as a datetime

Something like this maybe…harder to know without seeing the table structure and sample data.

SELECT posts.post_title
      ,MIN(CAST(meta.meta_value as DATETIME)) event_start_date 
  FROM wp_posts posts
  LEFT JOIN wp_postmeta meta
         ON posts.ID = meta.post_ID
WHERE posts.post_type ='Tribe_Events' 
  AND posts.post_status = 'publish'
  AND COALESCE(meta.meta_key, 'UNKNOWN') ='_EventStartDate'
GROUP BY posts.post_title;

This version can cope with multiple instances of '_EventStartDate' per post_title

Not sure if this is needed but it causes no harm. Just think of it as wearing a belt and suspenders.