Combining join results with an at least function
I'm new to SQL and have managed to pick up the basic functions capably enough, however I'm now trying to find the people with at least two tokens from the results of an inner join:
SELECT users.[First Name], users.[Last Name], IssuedTokens.UserID, IssuedTokens.TokenID, Tokens.TokenType FROM IssuedTokens INNER JOIN users ON users.ID = IssuedTokens.UserID INNER JOIN Tokens ON Tokens.number = IssuedTokens.TokenID GROUP BY IssuedTokens.UserID HAVING COUNT(*) >= 2 ORDER BY IssuedTokens.UserID
This gives the error:
Column 'Users.First Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm comfortable using functions on pre-existing tables, but have not seen how to manipulate the results of a join. If anyone could help it would be much appreciated.
You can do a separate aggregation -- before the join -- to get the users with multiple tokens. Then, the rest of the query doesn't need an aggregation:
SELECT u.[First Name], u.[Last Name], u.UserID, it.TokenID, t.TokenType FROM IssuedTokens it INNER JOIN users u ON u.ID = it.UserID INNER JOIN Tokens t ON t.number = it.TokenID INNER JOIN (SELECT it.UserId FROM IssuedTokens it GROUP BY it.UserId HAVING COUNT(*) >= 2 ) itu ON itu.UserId = it.UserId ORDER BY it.UserID;