I am trying to create a query in order to get all the metadata from a custom post type named match_reports that is associated with a meta_key and meta_value in another custom post type named team_page. The common denominator is the id of team_page which is a meta_value in match_reports. Therefore each team has match reports associated with it whether they are the home or away team. Eventually the results will be looped through in a table so that each team is listed next to its own statistics.
I've tried using WP_Query to do this but without much luck: Looping through tabular data
and today I've been trying with $wpdb where I was having some trouble with the syntax:
Here is what I've come up with so far:
$querydetails = "
FROM $wpdb->posts wposts,
INNER JOIN $wpdb->postmeta wpostmeta.* ON wposts.ID = wpostmeta.post_id
WHERE wposts.post_status = 'publish'
AND wposts.post_type = 'match_report'
AND wpostmeta.meta_key = 'report_home-select'
AND wpostmeta.meta_value = '198'
$teaminfo = $wpdb->get_results($querydetails, OBJECT);
I would love any help or suggestions on the best way to approach this. I know it can be done but its a bit beyond me. I'm also happy to read any tutorials or articles on dealing with $wpdb or complex queries using WP_Query.