SQL: Search query to get attachments only of those parents which are published

Question

i'm having some problem creating nice 1 query to be able to get search results which includes NOT only post_type POST but also attachments. BUT if search query return attachment, i want that it won't be returned if that's attachment's parent is NOT published.

I'm using ajaxy-search plugin and i want to customize it to search what i want. I pasted query which works for me, but i want to exclude attachments which parents are not published.

$results = $wpdb->get_results(
    $wpdb->prepare(
        "select $wpdb->posts.ID,
            $wpdb->posts.post_status,
            $wpdb->posts.post_parent
        from $wpdb->posts
        where post_title like '%%%s%%' and
            (post_type='albumas' OR post_mime_type='audio/mpeg') 
            AND (post_status = 'inherit' OR post_status = 'publish')
        $excludes limit 0,".$setting->limit,
        ($setting->search_content == true ? array($name, $name):$name)
    )
);                                                                             `

I know how to do that, but i want to get the most optimal code here, not executing in loop queries to check post_parent id and then if it's published. I want as much performance friendly code as it can be :D

Answer 1

Well since nobody helped with at first look such an easy problem, i used my way, and if somebody will need it here it is:

I used this query:

$results = $wpdb->get_results(
$wpdb->prepare(
    "select $wpdb->posts.ID,
        $wpdb->posts.post_status,
        $wpdb->posts.post_parent
    from $wpdb->posts
    where post_title like '%%%s%%' and
        (post_type='albumas' OR post_mime_type='audio/mpeg') 
        AND (post_status = 'inherit' OR post_status = 'publish')
    $excludes limit 0,".$setting->limit,
    ($setting->search_content == true ? array($name, $name):$name)
));

$results returns me not only post ID but also post_parent so i used condition in my php code

if($result->post_parent > 0)
  $pst = get_post($result->post_parent);

and then i just checked if the parent post is published:

if($pst != null && $pst->post_status =='publish') {

This is not very optimal version, but because plugin already was using get_post - so i did what i want this way. It would be nice to know how can we do this only via SQL query.