Rudi Ahlers wrote:
A google search results suggested the alias should be put after the bracket. So, I change the code as follow, and move the *AS `u`* outside the right bracket:
SELECT c . * , COUNT( m.id ) AS `members` FROM `jos_mls_teams` AS `c` LEFT JOIN `jos_mls_teams_members` AS `m` ON m.teamid = c.id RIGHT JOIN ( SELECT u.name, u.lastvisitDate FROM `jos_users` ) AS `u` ON u.id = m.userid GROUP BY c.id LIMIT 0 , 30
But then I get the error:
#1054 - Unknown column 'u.name http://u.name' in 'field list'
Inside the subquery, the table is jos_users (and since there is only one you shouldn't even have to specify it. The 'u' alias is a temporary table holding the result from the subquery.