I have a query, and I'm looking for a way to do a subquery and have the results joined as the last column

Something like this : My current query works and is similar to this:

SELECT * FROM users_table JOIN randomjoin here WHERE blah

The second query that I run and would like to integrate into the first query is:

SELECT COUNT(*) FROM #__community_fields_values WHERE field_id="16" AND value="'. $row->username .'"

So what I want to do is create an extra column in the result that displays the count of another query. The whole point is so that I could sort the results by the last column

Can someone help me figure it out?

• mysql

You can JOIN against a subquery, as long as it gets an alias.

SELECT 
  users_table.*,
  usercount.num
FROM 
  users_table
  LEFT JOIN (
    /* Added the value column and a GROUP BY to the subquery to return usernames & counts. */
    SELECT 
      COUNT(*) AS num, 
      value 
    FROM #__community_fields_values 
    WHERE field_id=16 
    GROUP BY value
  ) usercount ON users_table.username = usercount.value