Unsure how to obtain the data i require

[Gobble45]

Hi All,

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.

The question:

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?

[Gobble45]

Well, i've managed to sort this one myself. Im not sure if its the cleanest method, but it presents the correct results.

The query i came up with is:

SELECT `GG_Name` AS 'GG Name', `GG_Title` AS 'GG 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`, `opposition`
WHERE `GG_Begin` >= '1930-01-01' 
AND `GG_Begin` <= '1960-01-01'
AND (`opposition`.`Op_ldr_begin` >= `governor_general`.`gg_begin` AND `opposition`.`op_ldr_begin` <= `governor_general`.`gg_end`  OR `opposition`.`Op_ldr_end` >= `governor_general`.`gg_begin`  AND `opposition`.`op_ldr_end` <= `governor_general`.`gg_end` OR `opposition`.`op_ldr_begin` <= `governor_general`.`gg_begin` AND `opposition`.`op_ldr_end` >= `governor_general`.`gg_end` )
GROUP BY `GG_Name`
ORDER BY `GG_Begin` ASC

[rajkumar007]

Hello, can i get help in solving this question .

Who are the Opposition Leaders who subsequently became Prime Minister after 1930? List their name, the date they were elected Opposition Leader and the date they were elected Prime Minister and their Deputy Prime Minister’s name and the party that they led. The dates must be formatted as day of the week, day of the month, month in digits and year in four digits; eg. Monday, 01/01/1901. Order the list by ascending date of appointment as Opposition Leader..