Query between 2 tables I want the date for the last login for a defined role, but I have a problem with my query with drupal 7.

I have the good answer in phpmyadmin with this query :

SELECT login FROM `users` AS u, `users_roles` AS r
WHERE u.uid = r.uid
AND r.rid = 3
ORDER BY login DESC LIMIT 1


But an error with Drupal7... I tried this code :s :

$query = db_select('users','u')
 -&gt;join('users_roles','r','r.uid = u.uid')
 -&gt;fields('u',array('uid'))
 -&gt;fields('r',array('rid'))
 -&gt;condition('r.rid', '3', '=')
 -&gt;orderBy('login', 'DESC')
 -&gt;range(0,1);
$res = $query-&gt;execute();


Thanks

Edit : Thank for your answer Clive , this code works :

$query = db_select('users','u');
$query-&gt;join('users_roles','r','r.uid = u.uid');
$query-&gt;fields('u',array('login'))
 -&gt;condition('r.rid', '3', '=')
 -&gt;orderBy('login', 'ASC')
 -&gt;range(0,1);

$res = $query-&gt;execute();      
return $res-&gt;fetchField();
SelectQuery::join() returns the alias created, not the query object, so it can't be chained. This should work

$query = db_select('users','u')
 -&gt;fields('u',array('uid'))
 -&gt;fields('r',array('rid'))
 -&gt;condition('r.rid', '3', '=')
 -&gt;orderBy('login', 'DESC')
 -&gt;range(0,1);

$query-&gt;join('users_roles','r','r.uid = u.uid');
$res = $query-&gt;execute();