I have 3 tables to extract data from. My query thus far is:
SELECT `ministry`.`pm_name` AS 'Name', DATE_FORMAT(`ministry`.`min_begin`, '%Y') AS 'Election Year', `ministry`.`party` AS 'Party Led', `deputy_name` AS 'Deputy PM', COUNT(DISTINCT `ministry`.`min_begin`) AS 'Times Elected PM', `GG_name` as 'GG Name', `gg_title` as 'GG Title' FROM `ministry` JOIN `deputy_pm` ON `ministry`.`min_nr` = `deputy_pm`.`min_nr` LEFT JOIN `governor_general` ON `ministry`.`pm_name` = `governor_general`.`pm_name` WHERE `min_begin` <= '1930-01-01' AND `ministry`.`party` <> 'ALP' GROUP BY `ministry`.`pm_name`
Which returns the correct results of 7 rows. However the 7th row, in the GG_name and GG_title fields are null.
This is because in the governor_general table there is no mention of this particular PM, because a certain Governor General was in position over the time this PM was elected.
Ive tried a few different combinations of joins and select statements, and also the where statement, but nothing fixes the result.
The GG_Name and GG_Title should return the Governor General at the time the Prime minister was elected.
Any help is appreciated.