Custom search Dashboard widget

Firstly, let’s start off by cleaning up the query you have above. I’ve put it into Heredoc syntax to make it easier to read.

$sql = <<<SQL
SELECT *
FROM {$tablename} -- you should use interpolation when working in a string that can have variables
WHERE
( -- Your query will only ever match one row of meta_key at a time, so combine these and OR them
    meta_key = '_child_name'  -- changed LIKE to = since you're not doing a wildcard match
OR
    meta_key LIKE '%_child_name_%'
)
AND -- you only need to check against this once
    meta_value LIKE '%{$s_query}%'
LIMIT 0,100;
SQL;

$results = $wpdb->get_results( $sql );

Going through the comments:

• The WordPress coding standards recommend using {} brackets when doing string interpolation when using variables inside of "" double quotes or Heredoc syntax.
• You were querying for meta_key = '_child_name' and meta_key LIKE '%_child_name_%'. Since SQL can only return one row per match of meta_key, I put these into a single condition and OR‘d them.
• You were doing meta_key LIKE '_child_name' – since you weren’t matching a wildcard (%), the LIKE was superfluous.
• You had meta_value LIKE '%$s_query%' twice, you only need to do that once since you can only ever get one row matching back at a time (e.g. doing meta_value="A" AND meta_value="B" would always return zero results). Also note the use of interpolation wrapped in {} here.

One more note, the coding standards for WordPress would also encourage you to escape your query properly using methods like esc_like or $wpdb->prepare(), but that’s beyond the scope of this answer.

Restricting By Post Type

Post meta rows relate to the post table via the post ID, so to restrict your results to the shop-subscription post type, we can JOIN the posts table like so:

SELECT *
FROM {$tablename}
JOIN wp_posts p ON (p.post_type="shop-subscription" AND p.ID = wp_postmeta.post_id)
WHERE
(
    meta_key = '_child_name' 
OR
    meta_key LIKE '%_child_name_%'
)
AND
    meta_value LIKE '%{$s_query}%'
LIMIT 0,100;

This assumes you’re pulling rows from the wp_postmeta table (as the value of $tablename). If you’re using a different table, you should properly update teh query to relate the post ID to the meta row ID column.