How to change the query for content type in d6 to d7? I have a code in D6 which is as given below

In Drupal 6 for each content type we had seperate table like content_type_[content_name] 
--------------Drupal 6---------------------

    SELECT nid, field_id_value, field_oid_value,field_sts_value 
    FROM {content_type_bstatus} fsa INNER JOIN
    ( SELECT MAX(nid) as maxnid , field_oid_value  // subquery
      FROM {content_type_bstatus}
      GROUP BY field_oid_value
    ) fsb
    ON fsa.nid = fsb.maxnid
WHERE fsa.field_sts_value &lt;&gt; 'success' 




In Drupal D7 we have seperate table for each fields within the content type like 
 field_data_[field_name] 
and content_type is included only in nodes table
So, I am trying to implement the above code in D7

In Drupal D7 what i have tried is as follows but could not the exact solution 

------------------Drupal 7----------------------------------
For the subquery above I have implemented the code below 

SELECT MAX( entity_id ) , field_oid_value
FROM  `field_data_field_oid` 
GROUP BY field_oid_value


How can I add the rest of code ..I have tried something which does not work 
How can I write the code in d6 to d7 . I am using db_query in d6 and would use db_query or db_select but 
HOW CAN I USE THE CONTENT TYPE FIELDS WHICH ARE HAVING SEPERATE TABLES in d7 database structure??
You should add a new view by the UI. Then, you can display the generated SQL query, by activating the sql display below the views UI:

admin/build/views/tools (D6)
admin/structure/views/settings (D7)




You'll see live, what the sql query is :



Copy/Paste the sql and rearrange it