Calculating remaining amount of Spots left in Football Team, Using query.

Question

Hi, i'm trying to find out how to create a query to show the amount of spots that are remaining in a football team. First off there is 300 spots i can only make my query show how many spots have already been filled and can't seem to show the remaining. Hoping someone can shed some light on this for me.

To show the spots filled already i am using 2 fields from 2 tables and using the Totals feature to count the amount of spots filled.

Thanks

Answer 1

What is the SQL statement of the query which returns the number of filled spots?

---

Ken Sheridan, Stafford, England

Answer 2

Looks like this:

SELECT Team.Spot, Sum(Applicant.Number) AS SumOfNumber
FROM Team INNER JOIN [Applicant] ON Team.PlayerID = Team.PlayerID
GROUP BY Team.Spot
ORDER BY Team.Spot, Sum(Applicant.Number);

Thanks.

Answer 3

Hi Big Dobbie 420,

why you are matching the Team.PlayerID=Team.PlayerID.

its from the same table.

you need to select the playerid from the other table.

like Team.PlayerID=Applicant.PlayerID.

example query

SELECT CategoryName, ProductName
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID;

Reference:

INNER JOIN Operation

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

SELECT Team.Spot, Sum(Applicant.Number) AS SumOfNumber
FROM Team INNER JOIN [Applicant] ON Team.PlayerID = Team.PlayerID
GROUP BY Team.Spot
ORDER BY Team.Spot, Sum(Applicant.Number);

As Deepak points out that doesn't make a lot of sense.  I would have expected there to be a one-to-many relationship type between teams and applicants, with the Applicant table including a TeamID foreign key column referencing the primary key TeamID column of the Team table.

Can you post full details of the two table definitions, listing all columns from each and indicating the columns on which the tables are related.

---

Ken Sheridan, Stafford, England