By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE registrar (
`id` INTEGER,
`name` VARCHAR(4)
);
INSERT INTO registrar
(`id`, `name`)
VALUES
('1', 'reg1');
CREATE TABLE registrar_has_division (
`id` INTEGER,
`registrar_id` INTEGER,
`division_id` INTEGER,
`is_acting` INTEGER
);
INSERT INTO registrar_has_division
(`id`, `registrar_id`, `division_id`, `is_acting`)
VALUES
('1', '1', '10', '0'),
('2', '1', '11', '0'),
('3', '1', '12', '1'),
('4', '1', '13', '1');
Records: 4 Duplicates: 0 Warnings: 0
CREATE TABLE registrar_division (
`id` INTEGER,
`name_english` VARCHAR(12),
`div_type` INTEGER
);
INSERT INTO registrar_division
(`id`, `name_english`, `div_type`)
VALUES
('10', 'Hulftsdrop', '1'),
('11', 'Modara', '2'),
('12', 'Slave Island', '1'),
('13', 'Fort', '2');
Records: 4 Duplicates: 0 Warnings: 0
SELECT
r.id AS rid,
r.name,
MAX(IF(
divi.div_type = 1 && rd.is_acting = 0,
divi.name_english,
NULL
)) AS marriage_div,
MAX(IF(
divi.div_type = 2 && rd.is_acting = 0,
divi.name_english,
NULL
)) AS bd_div,
MAX(IF(
divi.div_type = 1 && rd.is_acting = 1,
divi.name_english,
NULL
)) AS acting_marriage_div,
MAX(IF(
divi.div_type = 2 && rd.is_acting = 1,
divi.name_english,
NULL
)) AS acting_bd_div
FROM
`registrar` AS `r`
INNER JOIN `registrar_has_division` AS `rd`
ON
`rd`.`registrar_id` = `r`.`id`
INNER JOIN `registrar_division` AS `divi`
ON
`rd`.`division_id` = `divi`.`id`
GROUP BY r.id,r.name
| rid | name | marriage_div | bd_div | acting_marriage_div | acting_bd_div |
|---|---|---|---|---|---|
| 1 | reg1 | Hulftsdrop | Modara | Slave Island | Fort |