Member Avatar for Gobble45

Hi there,

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.

  • 1 Contributor
  • 1 Reply
  • 288 Views
  • 39 Minutes Discussion Span
  • Latest Post Latest Post by Gobble45
Member Avatar for Gobble45

Yet again, minutes after posting a question on Daniweb i figure the damn thing out!

for anyone else with problems similar to this, my final query was:

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`.`min_begin` BETWEEN `gg_begin` AND `gg_end`
WHERE `min_begin` <= '1930-01-01'
AND `ministry`.`party` <> 'ALP'
GROUP BY `ministry`.`pm_name`
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.