I am using someone else's WP plugin which stores lots of related data in
wp_postmeta using an "artificial key". So effectively we hijack
wp_postmeta to create a nested relational database. So one row will have
meta_id = 999 post_id = 130 meta_key = 'event_id' meta_value = '19'
Then 4 other actual wp_postmeta rows that share that post_id contain the rest of the information for that virtual "row" of data. For example
meta_id = 989 post_id = 130 meta_key = 'Name' meta_value = 'Fred'
And we will have lots of "virtual rows" (hundreds) that all share that
event_id of 19 (and of course lots of others with different
event_ids of their own.
I have tried many SQL queries, but I am stumped. How can I get a table that has grouped info for a common event_id? It doesn't have to have all the data, just one piece, such as the 'Name' field. I tried a subquery like this:
SELECT meta_value from wp_postmeta WHERE meta_key='name' AND post_id IN (SELECT post_id FROM wp_postmeta WHERE meta_key='event_id' AND meta_value='19');
That probably looks laughably stupid - it also doesn't work. I also tried JOIN. This seems like something that would come up a lot in WP coding, but I have searched and come up pretty much empty.