I've got 4 tables:
divisions (RecordID, Division)
teams (RecordID, Team)
scores(RecordID, RelationID, Score)
relations (RecordID, 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 RecordID 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 combination of divisions.
They must be interchangable (ie divisions per team)
My Sql is:
SELECT DISTINCT divisions.Division, teams.Team, AVG(scores.Score) AS Score
FROM (scores
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.
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for divisions
-- ----------------------------
DROP TABLE IF EXISTS `divisions`;
CREATE TABLE `divisions` (
`RecordID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Division` varchar(50) DEFAULT NULL,
`boolIsActive` tinyint(4) unsigned DEFAULT '1',
PRIMARY KEY (`RecordID`),
UNIQUE KEY `Division` (`Division`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for relations
-- ----------------------------
DROP TABLE IF EXISTS `relations`;
CREATE TABLE `relations` (
`RecordID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`RelationID` int(11) unsigned DEFAULT NULL,
`FieldID` int(11) unsigned DEFAULT NULL,
`ValID` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`RecordID`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
`RecordID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`RelationID` int(11) DEFAULT NULL,
`Score` int(11) DEFAULT NULL,
PRIMARY KEY (`RecordID`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for teams
-- ----------------------------
DROP TABLE IF EXISTS `teams`;
CREATE TABLE `teams` (
`RecordID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`OrgID` int(11) DEFAULT NULL,
`Team` varchar(50) DEFAULT NULL,
`boolIsActive` tinyint(4) unsigned DEFAULT '1',
PRIMARY KEY (`RecordID`),
UNIQUE KEY `Team` (`Team`,`OrgID`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `divisions` VALUES ('1', 'Div One', '1');
INSERT INTO `divisions` VALUES ('2', 'Div Two', '1');
INSERT INTO `divisions` VALUES ('3', 'Div Three', '1');
INSERT INTO `divisions` VALUES ('4', 'Div Four', '1');
INSERT INTO `divisions` VALUES ('5', 'Div Five', '1');
INSERT INTO `relations` VALUES ('1', '1', '1', '1');
INSERT INTO `relations` VALUES ('2', '1', '1', '2');
INSERT INTO `relations` VALUES ('3', '1', '1', '2');
INSERT INTO `relations` VALUES ('4', '1', '2', '1');
INSERT INTO `relations` VALUES ('5', '1', '2', '2');
INSERT INTO `relations` VALUES ('6', '2', '1', '2');
INSERT INTO `relations` VALUES ('7', '2', '1', '3');
INSERT INTO `relations` VALUES ('8', '2', '1', '4');
INSERT INTO `relations` VALUES ('9', '2', '2', '2');
INSERT INTO `relations` VALUES ('10', '2', '2', '3');
INSERT INTO `relations` VALUES ('11', '3', '1', '1');
INSERT INTO `relations` VALUES ('12', '3', '1', '3');
INSERT INTO `relations` VALUES ('13', '3', '1', '4');
INSERT INTO `relations` VALUES ('14', '3', '2', '1');
INSERT INTO `relations` VALUES ('15', '3', '2', '3');
INSERT INTO `relations` VALUES ('16', '3', '2', '5');
INSERT INTO `scores` VALUES ('1', '1', '6');
INSERT INTO `scores` VALUES ('2', '1', '5');
INSERT INTO `scores` VALUES ('3', '1', '7');
INSERT INTO `scores` VALUES ('4', '1', '6');
INSERT INTO `scores` VALUES ('5', '2', '4');
INSERT INTO `scores` VALUES ('6', '2', '5');
INSERT INTO `scores` VALUES ('7', '2', '7');
INSERT INTO `scores` VALUES ('8', '2', '5');
INSERT INTO `scores` VALUES ('9', '3', '6');
INSERT INTO `scores` VALUES ('10', '3', '5');
INSERT INTO `scores` VALUES ('11', '3', '7');
INSERT INTO `scores` VALUES ('12', '3', '7');
INSERT INTO `teams` VALUES ('1', null, 'My Team One', '1');
INSERT INTO `teams` VALUES ('2', null, 'My Team Two', '1');
INSERT INTO `teams` VALUES ('3', null, 'My Team Three', '1');
INSERT INTO `teams` VALUES ('4', null, 'My Team Four', '1');
INSERT INTO `teams` VALUES ('5', null, 'My Team Five', '1');
INSERT INTO `teams` VALUES ('6', null, 'My Team Six', '1');