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.

Answers


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;

Need Your Help

Pass Quotes from a text box to external URL

vb.net textbox

I have a simple VB.net app that has a text box that I pass to a URL for searching. So let's say I want to send this: