Q:

mySQL record with latest status=1 in second table

I have table A
|    id    |    Name    |
_________________________
|    1     |    ABC     |
_________________________
|    2     |    BCD     |
_________________________

Table B
|    id    |    a_id    |    Status    |    timestamp    |
__________________________________________________________
|    1     |    1       |       1      |    timestamp    |
__________________________________________________________
|    2     |    1       |       2      |    timestamp    |
__________________________________________________________
|    3     |    1       |       3      |    timestamp    |
__________________________________________________________
|    4     |    2       |       1      |    timestamp    |
__________________________________________________________

In above example I only want to pick 
|    name  |    a_id    |    Status    |    timestamp    |
__________________________________________________________
|    BCD   |    2       |       1      |    timestamp    |

So with any record that has ONLY and ONLY latest STATUS as 1, I want to pick that record.. If the LATEST STATUS is 2 or 3, I don't want ot pick them...
If possible I don't want to use sub-query because I am using Codeigniter which doesn't really like subqueries. 
Please help 

A:

So that will be:
SELECT
  A.name,
  B.*
FROM
    (SELECT 
       a_id, 
       MAX(`timestamp`) AS max_timestamp
     FROM B 
     GROUP BY a_id) AS latest
    LEFT JOIN B
      ON 
        B.a_id=latest.a_id
        AND
        B.`timestamp`=latest.max_timestamp
    LEFT JOIN A
      ON B.a_id=A.id
WHERE
  B.status=1

As for subquery - I doubt you can retrieve desired information without that in single query. That is because you need to use row set, which is a result of grouping by one column, in reference to another column. And so you need to group that first, and then apply JOIN to result row set.

