Firstly I will say that this exercise is for my learning experiences through an online course. However I have spent a combined total of about 6 hours working on this question and I am completely stumped on how to get the correct answers.
List the Title, Name and date of appointment for Governors General of Australia who were appointed between 01 January 1930 and 01 January 1960, together with the Prime Ministers who appointed them and all Leaders of the Opposition who served during their appointment. The date is to be formatted in dd/mm/yyyy format. Order by ascending date of appointment.
So easy goes, one step at a time.
SELECT `GG_Name` AS 'GG Name', `GG_Title` AS 'GG Title', DATE_FORMAT(`GG_Begin`, '%d/%m/%Y') AS 'Date of Appointment' FROM `Governor_General`
Then I add in some filters:
SELECT `GG_Name` AS 'GG Name', `GG_Title` AS 'GG Title', DATE_FORMAT(`GG_Begin`, '%d/%m/%Y') AS 'Date of Appointment', `PM_name` AS 'Appointed By' FROM `Governor_General` WHERE `GG_Begin` >= '1930-01-01' AND `GG_Begin` <= '1960-01-01' ORDER BY `GG_Begin` ASC
And now i have all the data (except for the Opp Leaders), filtered and sorted.
The tricky part now, is the Opp Leader information is in another table called
'opposition' Instinctively i thought of a
JOIN. Which has been the bane of my existence ever since learning MySQL and working with databases. But as the questions says i need a list of those Opp Leaders in the time that each Governor General was appointed. For example if GG_A was GG from 1/1/2013 to 30/6/2013, i need all the Opp Leaders that were active during that time.
So i did some searching and found the
GROUP_CONCAT function, which works awesomely! so if i add in this function to my query, we have:
SELECT `GG_Name` AS 'GG G Name', `GG_Title` AS 'GG G Title', DATE_FORMAT(`GG_Begin`, '%d/%m/%Y') AS 'Date of Appointment', `governor_general`.`pm_name` AS 'Appointed By', GROUP_CONCAT(`opposition`.`Op_Ldr_Name` SEPARATOR ', ') AS 'Opposition Leaders' FROM `Governor_General` JOIN `opposition` ON `Governor_general`.`pm_name`=`opposition`.`pm_name` WHERE `GG_Begin` >= '1930-01-01' AND `GG_Begin` <= '1960-01-01' GROUP BY `GG_Title` ORDER BY `GG_Begin` ASC
However this results with the GROUP_CONCAT retrieving the OPP_LEADERS that have the same PM_NAME as the original GG.
Can anyone shed some light on how i would do this? or even tell me what is better to use, the join or union method?