I've got 4 tables:
divisions (ID, Division)
teams (ID, Team)
scores(ID, RelationID, Score)
relatives (ID, RelationID, FieldID, ValID)
The relatives 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 | Tomcats | 17 Southwest Division | Bears | 12 Northeast Division | Tomcats | 20 Northeast Division | Lions | 8 Northeast Division | Cheetahs | 13 Southeast Division | Cheetahs | 19 Southeast Division | Lions | 12 Southeast Division | Zebras | 6
Any given team can be in any division.
My Sql is:
SELECT DISTINCT divisions.Division, teams.Team, AVG(scores.Score) AS Score FROM (scores INNER JOIN ((relatives INNER JOIN divisions ON relatives.FieldID = 1 AND relatives.ValID = divisions.ID) INNER JOIN teams ON relatives.FieldID = 2 AND relatives.ValID = teams.ID) ON scores.RelationID = relatives.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.
Yes, the records are in the database. They're loaded for testing
How can I make the tables return what I need? I'd redesign the whole thing if needed.