CREATE TABLE `itemTypes` ( `itID` int(11) NOT NULL AUTO_INCREMENT, `itemType` varchar(255) NOT NULL, PRIMARY KEY (`itID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `itemTypes` (`itID`, `itemType`) VALUES (1, 'Item 1'), (2, 'Item 2'), (3, 'Item 3'); CREATE TABLE `clients` ( `cID` int(11) NOT NULL AUTO_INCREMENT, `clientName` varchar(255) NOT NULL, PRIMARY KEY (`cID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `clients` (`cID`, `clientName`) VALUES (1, 'Client 1'); CREATE TABLE `userGroups` ( `ugID` int(11) NOT NULL AUTO_INCREMENT, `userGroup` varchar(255) NOT NULL, PRIMARY KEY (`ugID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `userGroups` (`ugID`, `userGroup`) VALUES (1, 'Group 1'), (2, 'Group 2'); CREATE TABLE `users` ( `uID` int(11) NOT NULL AUTO_INCREMENT, `user` varchar(255) NOT NULL, `cid` int(11) NOT NULL, `ugID` int(11) NOT NULL, PRIMARY KEY (`uID`), KEY `cid` (`cid`), KEY `ugID` (`ugID`), CONSTRAINT `users_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `clients` (`cID`), CONSTRAINT `users_ibfk_2` FOREIGN KEY (`ugID`) REFERENCES `userGroups` (`ugID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `users` (`uID`, `user`, `cid`, `ugID`) VALUES (1, 'Fred', 1, 1), (2, 'Sam', 1, 2); CREATE TABLE `clientItemTypes` ( `cID` int(11) NOT NULL, `itID` int(11) NOT NULL, UNIQUE KEY `cID_itID` (`cID`,`itID`), KEY `itID` (`itID`), CONSTRAINT `clientItemTypes_ibfk_1` FOREIGN KEY (`cID`) REFERENCES `clients` (`cID`), CONSTRAINT `clientItemTypes_ibfk_2` FOREIGN KEY (`itID`) REFERENCES `itemTypes` (`itID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `clientItemTypes` (`cID`, `itID`) VALUES (1, 1), (1, 2), (1, 3); CREATE TABLE `userGroupItemTypes` ( `ugID` int(11) NOT NULL, `itID` int(11) NOT NULL, UNIQUE KEY `ugID_itID` (`ugID`,`itID`), KEY `itID` (`itID`), CONSTRAINT `userGroupItemTypes_ibfk_1` FOREIGN KEY (`ugID`) REFERENCES `userGroups` (`ugID`), CONSTRAINT `userGroupItemTypes_ibfk_2` FOREIGN KEY (`itID`) REFERENCES `itemTypes` (`itID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `userGroupItemTypes` (`ugID`, `itID`) VALUES (2, 1), (2, 2);

SELECT u.uID, it.itID, u.user, it.itemType FROM users u INNER JOIN clientItemTypes cit ON cit.cID = u.CID INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID INNER JOIN itemTypes it ON it.itID = ugit.itID INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID WHERE c.total > 0 GROUP BY uID, itID UNION SELECT u.uID, it.itID, u.user, it.itemType FROM users u INNER JOIN clientItemTypes cit ON cit.cID = u.CID INNER JOIN itemTypes it ON it.itID = cit.itID INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID WHERE c.total = 0 GROUP BY uID, itID