Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Location
    Narre Warren
    Posts
    10

    Unanswered: Inner join, again

    Hi guys,

    Sorry to disturb you but I'm really stuck again with inner join's. As you can see, databases are definitely not my the strongest point.

    What I need this time is a statement to retrieve, for each Prime Minister, the year when they finally lost office.
    And the second question, how can I check that any Deputy Prime Minister subsequently become Prime Minister?

    Table 1:
    Code:
    +-------------+------------+
    | pm_name     | yrs_served |
    +-------------+------------+
    | Barton E    |       2.72 |
    | Bruce S M   |       6.71 |
    | Chifley J B |       4.42 |
    | Cook J      |       1.23 |
    | Curtin J    |       3.75 |
    | Deakin A    |       4.84 |
    | Fadden A W  |       0.11 |
    | Fisher A    |       4.81 |
    | Forde F M   |       0.02 |
    | Fraser J M  |       7.33 |
    | Gorton J G  |       3.17 |
    | Hawke R J L |       8.78 |
    | Holt H E    |       1.88 |
    | Hughes W M  |       7.29 |
    | Keating P J |          0 |
    | Lyons J A   |       7.25 |
    | McEwen J    |       0.06 |
    | McMahon W   |       1.72 |
    | Menzies R G |      18.41 |
    | Page E C G  |       0.05 |
    | Reid G H    |       0.87 |
    | Scullin J H |       2.17 |
    | Watson J C  |       0.28 |
    | Whitlam E G |       2.92 |
    +-------------+------------+

    Table 2.
    Code:
    +-------------+------------------+---------+
    | pm_name     | party            | yr_comm |
    +-------------+------------------+---------+
    | Barton E    | Protectionist    |    1901 |
    | Bruce S M   | Nationalist      |    1923 |
    | Cook J      | Liberal          |    1913 |
    | Deakin A    | Protectionist    |    1903 |
    | Deakin A    | Protectionist    |    1905 |
    | Deakin A    | Protectionist    |    1909 |
    | Fadden A W  | Country          |    1941 |
    | Fraser J M  | Liberal          |    1975 |
    | Fraser J M  | Liberal          |    1975 |
    | Fraser J M  | Liberal          |    1980 |
    | Fraser J M  | Liberal          |    1982 |
    | Fraser J M  | Liberal          |    1977 |
    | Gorton J G  | Liberal          |    1968 |
    | Gorton J G  | Liberal          |    1968 |
    | Gorton J G  | Liberal          |    1969 |
    | Holt H E    | Liberal          |    1966 |
    | Holt H E    | Liberal          |    1966 |
    | Hughes W M  | Nationalist      |    1918 |
    | Hughes W M  | Nationalist      |    1917 |
    | Hughes W M  | National labor   |    1916 |
    | Lyons J A   | United Australia |    1938 |
    | Lyons J A   | United Australia |    1932 |
    | McEwen J    | Country          |    1967 |
    | McMahon W   | Liberal          |    1971 |
    | Menzies R G | Liberal          |    1949 |
    | Menzies R G | United Australia |    1939 |
    | Menzies R G | United Australia |    1940 |
    | Menzies R G | United Australia |    1940 |
    | Menzies R G | Liberal          |    1951 |
    | Menzies R G | Liberal          |    1956 |
    | Menzies R G | Liberal          |    1958 |
    | Menzies R G | Liberal          |    1963 |
    | Page E C G  | Country          |    1939 |
    | Reid G H    | Free Trade       |    1904 |
    +-------------+------------------+---------+
    Cheers,
    Flybro

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    homework, again

    you really should try the join, and ask for help if you get an error or incorrect results

    don't forget to post your attempted sql

    other than tonkuma, nobody here is just going to hand you the answer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I understood what r937 was considering, now.

    So, I will not just give the answer, too.

  4. #4
    Join Date
    May 2012
    Location
    Narre Warren
    Posts
    10
    Hi guys,

    Perhaps I was misunderstod but I'm not looking for ready to go solution but more for a clue how to start, like e.g:
    Code:
    SELECT ... LEFT JOIN ... USING (pm_name)
    By he way, this is not a homework, I', definitely to old for that, just playing with some database for fun.

    Cheers,
    Flybro

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, clues...

    you want an INNER JOIN, not LEFT JOIN, since i doubt that you'd have a row in table 1 that doesn't have a matching row in table 2

    then once you have them joined properly, you need to do some date arithmetic, i.e. you need to add INTERVAL yrs_served YEAR to yr_comm

    as for your second question, no info given about deputy pms, no clue possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2012
    Location
    Narre Warren
    Posts
    10
    Thx mate,

    I made this in that way, perhaps it is not the most beautifully written piece of code but it works. If you can suggest how to improve it I will be really grateful.

    OK, let's go:

    Code:
    SELECT  t1.pm_name AS 'PM',
            t1.party AS 'Party',
            min(t1.yr_comm) AS 'Commenced Office',
            t2.yrs_served AS 'Years Served',
            (CAST(TRUNCATE(t2.yrs_served, 0) + min(t1.yr_comm) AS DECIMAL )) AS 'Finally Lost Office'
            
      FROM  ministry t1
    
      JOIN  prime_minister t2
        ON  (t1.pm_name = t2.pm_name)
     WHERE  t1.party <> 'Labor'
     
     GROUP BY PM
     ORDER BY yr_comm, PM ASC;
    Result:
    Code:
    +-------------+------------------+------------------+--------------+---------------------+
    | PM          | Party            | Commenced Office | Years Served | Finally Lost Office |
    +-------------+------------------+------------------+--------------+---------------------+
    | Barton E    | Protectionist    |             1901 |         2.72 |                1903 |
    | Deakin A    | Protectionist    |             1903 |         4.84 |                1907 |
    | Reid G H    | Free Trade       |             1904 |         0.87 |                1904 |
    | Cook J      | Liberal          |             1913 |         1.23 |                1914 |
    | Hughes W M  | National labor   |             1916 |         7.29 |                1923 |
    | Bruce S M   | Nationalist      |             1923 |         6.71 |                1929 |
    | Lyons J A   | United Australia |             1932 |         7.25 |                1939 |
    | Menzies R G | United Australia |             1939 |        18.41 |                1957 |
    | Page E C G  | Country          |             1939 |         0.05 |                1939 |
    | Fadden A W  | Country          |             1941 |         0.11 |                1941 |
    | Holt H E    | Liberal          |             1966 |         1.88 |                1967 |
    | McEwen J    | Country          |             1967 |         0.06 |                1967 |
    | Gorton J G  | Liberal          |             1968 |         3.17 |                1971 |
    | McMahon W   | Liberal          |             1971 |         1.72 |                1972 |
    | Fraser J M  | Liberal          |             1975 |         7.33 |                1982 |
    +-------------+------------------+------------------+--------------+---------------------+
    Cheers,
    Flybro

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •