Re: Help - complex joins

[Bad Programmer]

I can give you access to the test database but I'd need an IP to grant permission to access the db server. Some kind of a private message would help. I'd rather not broadcast the creds here.

I've tried multiple versions of joins. The closest I've gotten was with my SELECT in the JOIN which you see in my original post. It doesn't throw an error but the averages & counts are wrong.

The structure is pretty straight forward.
The FieldID in the atttrib_ tables is a foriegn key for the RecordID in the tables they relate to.

CREATE TABLE `answers` (
`RecordID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`AttributionID` int(11) DEFAULT NULL,
`RatingID` bigint(20) unsigned DEFAULT NULL,
`EmployeeID` bigint(20) DEFAULT NULL,
`Score` smallint(20) unsigned DEFAULT NULL,
`DateCreated` datetime DEFAULT NULL,
PRIMARY KEY (`RecordID`)
) ENGINE=MyISAM AUTO_INCREMENT=4761 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `division` (
`RecordID` smallint(4) unsigned NOT NULL AUTO_INCREMENT,
`Entry` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`RecordID`),
UNIQUE KEY `Entry` (`Entry`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `attrib_division_rl` (
`RecordID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`AttributionID` int(10) unsigned NOT NULL,
`FieldID` int(10) unsigned NOT NULL,
PRIMARY KEY (`RecordID`),
UNIQUE KEY `AttributionField` (`AttributionID`,`FieldID`)
) ENGINE=MyISAM AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `organization` (
`RecordID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`SubName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`DateCreated` datetime DEFAULT NULL,
PRIMARY KEY (`RecordID`),
UNIQUE KEY `OrgName` (`Name`,`SubName`)
) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `attrib_organization_rl` (
`RecordID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`AttributionID` bigint(20) unsigned DEFAULT NULL,
`FieldID` int(10) DEFAULT NULL,
PRIMARY KEY (`RecordID`),
UNIQUE KEY `AttributionID` (`AttributionID`,`FieldID`)
) ENGINE=MyISAM AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `employee` (
`RecordID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`EmployeeID` bigint(20) DEFAULT NULL,
`FName` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
`MidInit` varchar(1) COLLATE utf8_unicode_ci DEFAULT ' ',
`LName` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
`DateCreated` datetime DEFAULT NULL,
PRIMARY KEY (`RecordID`),
UNIQUE KEY `Name` (`FName`,`LName`,`MidInit`)
) ENGINE=MyISAM AUTO_INCREMENT=646 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci