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