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?
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