MSSQL: Query records that have less than 7 instances in another table?
I'm new to this and hoping this is easy for someone else, cause I can't get to work!
I'm using MS SQL Server 2005.
In one table I have the team names: id(pk), teamname.
In another table I have users: id(pk), firstname, lastname, teamid
I'm trying to create a query that will return the id and teamname from the team table, where the number of team members is < 7.
Here's what I'm trying, but it doesn't get me the right results and I don't think this accounts for teams that have zero users?
Any help is greatly appreciated,
SELECT a.id, a.teamname
FROM Teams a
WHERE a.id IN
FROM Users b
GROUP BY b.teamid
HAVING COUNT(*) < 7)
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)