php - How can I cross-reference a database table by itself? -

i've been trying find way (armed basic knowledge of php , wildly optimistic sense of determination) search wordpress database meta-values based on other meta-values.

i need item name 1 table (wp_posts) using post's id, use obtain post_id of items same name in table (wp_postmeta) , same table values particular custom field every post_id obtained.

since i'm new attempted breaking down parts , here's i've got.

<!--  part works , gets post title expected -->      <?php         $result1 =$wpdb->get_results("select post_title $wpdb->posts , $wpdb->postmeta  $wpdb->posts.id = $post->id", object);          print_r($result1[0]->post_title);     ?>  <!-- then, expected return post_id of entries title same 1 obtained , meta_key = custom_field_one. doesn't -->      <?php         $result2 =$wpdb->get_results("select post_id $wpdb->posts , $wpdb->postmeta  $wpdb->postmeta.meta_value = $result1 , $wpdb->postmeta.meta_key = custom_field_one", object );          print_r($result2->post_id);     ?>  <!-- so, unsurprisingly, part doesn't work either. should use every post_id obtained once again search postmeta table -->      <?php         $result3 =$wpdb->get_results("select meta_value $wpdb->posts , $wpdb->postmeta  $wpdb->postmeta.post_id = $result2 , $wpdb->postmeta.meta_key = custom_field_two", object );          print_r($result3->meta_value);     ?> 

since first query works, , subsequent ones slight variations on expected work. but, well, wrong.

incidentally, i've tried number of syntax variations in case issue , i'm aware there issues regarding arrays. advice appreciated.

use aliases:

select      a1.name,      b1.info table2 b1 join table2 a1 on b1.id= a1.id , a1.status = 1