Member Avatar for 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?

Edited by Gobble45 because: wrote 'sort' instead of 'order'
  • 2 Contributors
  • 2 Replies
  • 288 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by rajkumar007

Dani AI

Generated

Good call, — treating Governors General and Opposition Leaders as date ranges and testing for overlap is the right conceptual approach. The usual pattern is: 1) restrict governors by their appointment date, 2) join leaders where their service range overlaps the governor’s tenure, and 3) aggregate the matching leader names per governor so each governor row shows all relevant opponents.

A compact, clear template (using neutral names) that follows those principles:

SELECT
  g.id,
  g.name AS governor_name,
  DATE_FORMAT(g.start_date, '%d/%m/%Y') AS appointment_date,
  pm.name AS appointed_by,
  GROUP_CONCAT(DISTINCT ol.name ORDER BY ol.start_date SEPARATOR ', ') AS opposition_leaders
FROM governors g
LEFT JOIN prime_ministers pm ON pm.id = g.appointed_by_pm_id
LEFT JOIN opposition_leaders ol
  ON ol.start_date <= COALESCE(g.end_date, '9999-12-31')
  AND COALESCE(ol.end_date, '9999-12-31') >= g.start_date
WHERE g.start_date BETWEEN '1930-01-01' AND '1960-01-01'
GROUP BY g.id
ORDER BY g.start_date;

Troubleshooting and caveats:

  • Use a LEFT JOIN so governors with no matching opposition leaders still appear.
  • Normalize open-ended tenures (NULL end dates) with COALESCE to a far-future date when testing overlaps.
  • Use GROUP BY on the governor primary key (not text columns) to avoid ONLY_FULL_GROUP_BY problems; alternatively use ANY_VALUE() for non-aggregates.
  • Add DISTINCT inside GROUP_CONCAT to avoid duplicates and ORDER BY inside GROUP_CONCAT for deterministic lists.
  • For performance, ensure indexes exist on the date columns and foreign-key fields.
  • Format dates with DATE_FORMAT for display but ORDER BY the raw date column to guarantee correct chronological ordering.

For ’s follow-up, the same date-aware mindset applies: join the leadership and prime-minister appointment records on the person identifier (not name) and compare the relevant dates to find those who moved from Opposition Leader to Prime Minister.

Member Avatar for 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
Member Avatar for 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..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.