I'm getting WordPress database error: [Unknown column 'wp_posts.ID' in 'field list'], but column does exist, can someone please tell me what I'm doing wrong?

SELECT * FROM wp_posts WHERE ID IN(
    SELECT ID FROM
    (
        SELECT wp_posts.ID,wp_posts.post_date,d.name from wp_posts as a
        WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
        JOIN wp_term_relationships AS b ON ( a.ID = b.object_id)
        JOIN wp_term_taxonomy AS c ON (b.term_taxonomy_id = c.term_taxonomy_id)
        JOIN wp_terms AS d ON (c.term_id = d.term_id)
        WHERE c.taxonomy = 'my_custom_taxonomy'
        GROUP BY d.name 
        HAVING (wp_posts.post_date = max(wp_posts.post_date))
    )
    tmp
);