I have two db tables: wp_comments and wp_ratings. I'm trying to create a query using $wpdb which has to do the following:
from wp_comments I have to get the comment_ID's where the comment_post_ID is equal to the current post id to get all the ratings from wp_ratings where comment_id is equal to the comment_ID's i got from wp_comments.
wp_comments table is the standard wp table for comments. wp_ratings is custom and looks like this:
I'm struggling to determine wether to use JOIN or a subquery to achive this. I'm rather new to writing queries. So I only have a first part of a query so far:
$my_query = $wpdb -> get_results( "SELECT rating FROM wp_ratings WHERE comment_id =( SELECT comment_ID FROM comments WHERE comment_post_ID = " . the_ID () . " ) " );
I want the ratings to calculate (and show) the average value and to show the number of ratings.
EDIT: got the substringpart working:
$my_query = $wpdb -> get_results( "SELECT comment_ID FROM wp_comments WHERE comment_post_ID = $post->ID" );
found a comment somwhere that when you use double quotes, you can insert a variable directly without closing the string!