Is there any API to get corresponding field in a custom content type?
In this instance using db_query is appropriate...

$nids = db_query("SELECT nid FROM node WHERE title LIKE :title", array(':title' =&gt; '%'.$title.'%'))-&gt;fetchCol();

$nodes = node_load_multiple($nids);

foreach($nodes as $node) {
    $subtitle = $node-&gt;field_subtitle[LANGUAGE_NONE][0]['value'];
    // do things with the subtitle....
}


What has happened here is we are using db_query to search the node table for nodes with matching titles. Once we have the nids we call node_load_multiple and that loads the whole node(s). Drupal uses caching here so we don't have to necessarily connect to the DB at all to do this.

The resulting $node object has all of the fields already attached to it so knowing where they are actually stored in the DB at this point is irrelevant.

As Clive has pointed out we shouldn't really access field items directly as using field_get_times or EntityMetadataWrapper will ensure that we always load the field items in the correct language (the language of the current user).

To use field_get_items you could:

foreach($nodes as $node) {

    // using field_get_items here automatically loads the field in the correct language
    $field_items = field_get_items('node', $node, 'field_subtitle');
    $subtitle = $field_items[0]['safe_value'];

    // do things with the subtitle....
}