I have a database of politicians, political parties and candidacies for a post in the election. The database consists of the following tables: People, Groups, Candidacies and Posts. The Candidacies table is a join relationship between People and Groups. Each record in the Candidacies table represents a candidacy in the election and is linked to a particular post (record in the Posts table). Each post has a post type (senator, president, etc) from the PostTypes table and is linked to a constituency, in this case one of 10 departments (like a state) drawn from a table (Departments).
So, I have:
Table: PEOPLE; PK: id Table: GROUPS; PK: id Table: CANDIDACIES; PK: id, FKs: id_People, id_Groups, id_Posts Table: POSTTYPES; PH: id Table: POSTS; PK: id; FK: id_Department, id_PostTypes Table: DEPARTMENT; PK: id
I am trying to create a SQL statement that would select the total number of Candidacies of a particular post type (id_PostType = X) and post is a particular post (id_Post = X) from a particular department (id_Department = X).
I have it working when selected by Post, but can't seem to get it to further select by the Department. I think I may need to do another join or something, but I am lost.
Here is what I have:
SELECT g.name, COUNT(c."id") AS theCount FROM Candidacies c JOIN Groups g ON c."id_Groups" = g."id" JOIN People p ON c."id_People" = p."id" WHERE ( c."id_PostTypes" = "Senator" ) GROUP BY g."name" ORDER BY theCount
This appears to be selecting the names and total number of candidacies where the post type is Senator. How do I now add the criteria of the department of the particular post?