By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Person (
person_id INT AUTO_INCREMENT PRIMARY KEY,
person_name VARCHAR(255)
);
INSERT INTO Person VALUES (1,'Person A'), (2,'Person B'), (3,'Person C');
CREATE TABLE Profession (
profession_id INT AUTO_INCREMENT PRIMARY KEY,
profession_name VARCHAR(255)
);
INSERT INTO Profession VALUES
(11, 'actor'),
(22, 'screenwriter'),
(33, 'director'),
(44, 'premier minister'),
(55, 'president');
CREATE TABLE PersonToProfession (
person_id INT NOT NULL,
profession_id INT NOT NULL,
PRIMARY KEY (person_id, profession_id),
FOREIGN KEY (person_id) REFERENCES Person (person_id),
FOREIGN KEY (profession_id) REFERENCES Profession (profession_id)
);
INSERT INTO PersonToProfession VALUES
(1,11), (1,22), (1,33),
(2,11), (2,33), (2,44),
(3,22), (3,33), (3,55);
Records: 3 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
Records: 9 Duplicates: 0 Warnings: 0
SELECT person_name,
( SELECT profession_name
FROM Profession pr
JOIN PersonToProfession ptp USING (profession_id)
WHERE pe.person_id = ptp.person_id
ORDER BY pr.profession_name ASC LIMIT 1 ) profession_name
FROM Person pe
ORDER BY 1, 2;
| person_name | profession_name |
|---|---|
| Person A | actor |
| Person B | actor |
| Person C | director |
SELECT person_name, profession_name
FROM Person
JOIN PersonToProfession USING (person_id)
JOIN Profession USING (profession_id)
ORDER BY 2, 1;
| person_name | profession_name |
|---|---|
| Person A | actor |
| Person B | actor |
| Person A | director |
| Person B | director |
| Person C | director |
| Person B | premier minister |
| Person C | president |
| Person A | screenwriter |
| Person C | screenwriter |