MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);

Records: 3  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0


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


uID
itID
user
itemType


2
2
Sam
Item 2

2
1
Sam
Item 1

1
3
Fred
Item 3

1
2
Fred
Item 2

1
1
Fred
Item 1


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
WHERE EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
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
WHERE NOT EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID


uID
itID
user
itemType


2
1
Sam
Item 1

2
2
Sam
Item 2

1
3
Fred
Item 3

1
2
Fred
Item 2

1
1
Fred
Item 1