divisions (ID, Division)
teams (ID, Team)
scores(ID, RelationID, Score)
relations (ID, RelationID, FieldID, ValID)
The relations table is to 'link' divisions and teams by RelationID
The FieldID defines the table (1=divisions; 2=teams)
The ValID = the ID of the given table.
So, my fk is a combination of FieldID & ValID
The output should look something like this:
Southwest Division | Pneumatics | 17
Southwest Division | Refrigeration | 12
Northeast Division | Pneumatics | 20
Northeast Division | Underground | 8
Northeast Division | Networking | 13
Southeast Division | Networking | 19
Southeast Division | Underground | 12
Southeast Division | Efg | 6
Any given team can be in any number of divisions.
My Sql is:
SELECT DISTINCT divisions.Division, teams.Team, AVG(scores.Score) AS Score
INNER JOIN ((relations INNER JOIN divisions ON relations.FieldID = 1 AND relations.ValID = divisions.ID)
INNER JOIN teams ON relations.FieldID = 2 AND relations.ValID = teams.ID)
ON scores.RelationID = relations.RelationID)
GROUP BY Division, Team
ORDER BY Division, Team
This sql returns no records. Even if I remove the answers, GROUP and ORDER BY I get no records.
How can I make the tables return what I need? I'd redesign the whole thing if needed.