Using $wpdb to query posts with meta value containing current post_id

In code you posted you do not retrieve ‘list of posts’ as you say, but retrieve a list of rows in the meta table. If you want really retrieve a list of posts rely on WP_Query using meta_query param.

Something like:

$id = '10'; // unserialized value

$args = array(
  'post_type' => 'post',
  'post_status' => 'publish',
  'posts_per_page' => -1,
  'meta_query' => array(
    array(
      'key' => 'roles_%_production',
      'value' => $id,
      'compare' => 'LIKE'
    )
  )
);
$query = new WP_Query( $args );
$rows = $query->get_posts();

If you want to use $wpdb (I don’t know why) right query is something like:

<?php
$id = '10'; // unserialized value

global $wpdb;
$rows = $wpdb->get_col( $wpdb->prepare(
  "SELECT DISTINCT $wpdb->posts.ID FROM $wpdb->posts, $wpdb->postmeta
  WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id AND
  $wpdb->posts.post_status="publish" AND
  $wpdb->posts.post_type="post" AND
  $wpdb->postmeta.meta_key = %s AND
  meta_value = %s",
  'roles_%_production',
  $id
) );
?>

$rows will contain an array of posts IDs. I’ve done this change to make a sense to the use of $wpdb. If you want retrieve all the fields use SELECT * instead of SELECT DISTINCT $wpdb->posts.ID, use $wpdb->get_results instead of $wpdb->get_col and add the line GROUP BY $wpdb->posts.ID to the query.