Which query method to use? (edit- wpdb syntax problems)

Question

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:

global $wpdb;
$querydetails = "
  SELECT wposts.* 
  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.

Answer 1

This will work fine.

 global $wpdb;
 $querydetails = "
SELECT
    wposts.*
FROM 
    $wpdb->posts AS wposts
INNER JOIN 
    $wpdb->postmeta AS 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);

Try it.

Answer 2

I did end up finding the answer using WP_Query. In case anyone is curious: Looping through tabular data