By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TableSkills (`ID` INTEGER, `skill_code` VARCHAR(7));
✓
INSERT INTO TableSkills (`ID`, `skill_code`) VALUES
('1', 'Skill 1'),
('2', 'Skill 2'),
('3', 'Skill 3'),
('4', 'Skill 4'),
('5', 'Skill 5'),
('6', 'Skill 6');
✓
CREATE TABLE TableUsers (`ID` INTEGER, `User` VARCHAR(5), `Skill_1` INTEGER, `Skill_2` INTEGER, `Skill_3` INTEGER, `Skill_4` INTEGER, `Skill_5` INTEGER, `Skill_6` INTEGER);
✓
INSERT INTO TableUsers (`ID`, `User`, `Skill_1`, `Skill_2`, `Skill_3`, `Skill_4`, `Skill_5`, `Skill_6`) VALUES
('1', 'Mark', '1', '1', '0', '0', '0', '0'),
('2', 'John', '0', '0', '1', '0', '0', '0'),
('3', 'Doe', '0', '1', '1', '0', '0', '0'),
('4', 'Jason', '1', '1', '0', '0', '0', '0'),
('5', 'Kevin', '1', '1', '0', '0', '0', '0'),
('6', 'Mike', '0', '1', '1', '0', '0', '1');
✓
SELECT DISTINCT s.skill_code
FROM TableSkills s INNER JOIN TableUsers u
ON 1 = CASE s.ID
WHEN 1 THEN u.Skill_1
WHEN 2 THEN u.Skill_2
WHEN 3 THEN u.Skill_3
WHEN 4 THEN u.Skill_4
WHEN 5 THEN u.Skill_5
WHEN 6 THEN u.Skill_6
END;
| skill_code |
|---|
| Skill 1 |
| Skill 2 |
| Skill 3 |
| Skill 6 |
SELECT s.skill_code
FROM TableSkills s
WHERE EXISTS (
SELECT *
FROM TableUsers u
WHERE 1 = CASE s.ID
WHEN 1 THEN u.Skill_1
WHEN 2 THEN u.Skill_2
WHEN 3 THEN u.Skill_3
WHEN 4 THEN u.Skill_4
WHEN 5 THEN u.Skill_5
WHEN 6 THEN u.Skill_6
END
);
| skill_code |
|---|
| Skill 1 |
| Skill 2 |
| Skill 3 |
| Skill 6 |