SELECT Count(*) FROM Multiple Tables (Hard one)

Results 1 to 3 of 3

Thread: SELECT Count(*) FROM Multiple Tables (Hard one)

  1. #1
    Mauricio C. Guest

    Default SELECT Count(*) FROM Multiple Tables (Hard one)

    Hello ppl.<BR><BR><BR> I need to get the ID from a record whenever the count of records related to it (from another table) are not more than 50.<BR><BR><BR>So... The thing is, there&#039;s a table named "TEAMS" and another table named "USERS"... each team cannot have more than 50 users, so, every time a new user wants to be in a team, I must select the teams which don&#039;t have more than 49... (if there&#039;s no team, a new one will be created, but that&#039;s another tale).<BR>The TEAMS table has Columns: ID, Name. USERS Table has Columns: ID, Name, IDTeam.<BR><BR>This way, if you want to know how many users are on a team, you do a select count(*) as Total FROM Users WHERE IDTeam = ID--of the team, of course.<BR><BR>So.. how do I select the ID from a team where the number of users is lt 50? (count(*) &#060; 50)??<BR><BR> Thanks a lot.<BR>--Sorry if I&#039;m not very clear.

  2. #2
    Join Date
    Dec 1969

    Default RE: SELECT Count(*) FROM Multiple Tables (Hard one

    Try this<BR><BR>SELECT Teams.TeamId, <BR>Count(Users.Namez) AS CountOfNamez<BR>FROM Teams <BR>INNER JOIN Users ON Teams.TeamId = Users.TeamId<BR>GROUP BY Teams.TeamId<BR>HAVING (((Count(Users.Namez))&#060;50));<BR><BR><BR>Assum es that two tables exist Teams & Users <BR>and that the TeamId exists on Users table<BR>Namez can be any non blank (null) field in Users Table<BR><BR>R<BR>

  3. #3
    Mauricio C. Guest

    Default Yes it worked!! (not so hard then!)

    Thanks a lot. it worked perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts