Get data from database table by post_id to get data from second database table

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:

wp_ratings:

• id
• date
• comment_id
• user_id
• rating

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!