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

    Unanswered: [Solved] columns concatenation

    Hi,

    I'm just at the beginning of databaseing and having question about how to modify this query to display all recreations in one column. As you can see, some of the prime_minister have a few different recreations, I would like to display them in one column per name, e.g. Fraser fishing,vintage cars,photography

    Code:
    SELECT DISTINCT
        prime_minister_2000.prime_minister.pm_name,
        prime_minister_2000.ministry.party,
        prime_minister_2000.pm_recreation.recreation,
        prime_minister_2000.pm_marriage.spouse_name,
        prime_minister_2000.pm_marriage.nr_children
    FROM
        prime_minister_2000.prime_minister
    INNER JOIN
        prime_minister_2000.ministry
    ON
        (
            prime_minister_2000.prime_minister.pm_name = prime_minister_2000.ministry.pm_name)
    INNER JOIN
        prime_minister_2000.pm_recreation
    ON
        (
            prime_minister_2000.ministry.pm_name = prime_minister_2000.pm_recreation.pm_name)
    INNER JOIN
        prime_minister_2000.pm_marriage
    ON
        (
            prime_minister_2000.pm_recreation.pm_name = prime_minister_2000.pm_marriage.pm_name)
    WHERE
        prime_minister_2000.ministry.party <> 'Labor'
    AND prime_minister_2000.pm_marriage.nr_children >= 4
    ORDER BY
        prime_minister_2000.ministry.pm_name ASC,
        prime_minister_2000.ministry.party ASC ;
    Code:
    +------------+------------------+--------------+-------------+-------------+
    | pm_name    | party            | recreation   | spouse_name | nr_children |
    +------------+------------------+--------------+-------------+-------------+
    | Barton E   | Protectionist    | reading      | Ross JM     |           6 |
    | Fadden A W | Country          | golf         | Thornber I  |           4 |
    | Fraser J M | Liberal          | fishing      | Beggs T     |           4 |
    | Fraser J M | Liberal          | vintage cars | Beggs T     |           4 |
    | Fraser J M | Liberal          | photography  | Beggs T     |           4 |
    | Hughes W M | National labor   | motoring     | Cutts E     |           6 |
    | Hughes W M | National labor   | golf         | Cutts E     |           6 |
    | Hughes W M | National labor   | riding       | Cutts E     |           6 |
    | Hughes W M | Nationalist      | golf         | Cutts E     |           6 |
    | Hughes W M | Nationalist      | riding       | Cutts E     |           6 |
    | Hughes W M | Nationalist      | motoring     | Cutts E     |           6 |
    | Lyons J A  | United Australia | tennis       | Burnell E M |          11 |
    | Page E C G | Country          | tennis       | Blunt E     |           4 |
    +------------+------------------+--------------+-------------+-------------+
    Cheers,
    Flybr
    Last edited by Flybro; 05-07-12 at 00:03.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish the source data of
    prime_minister_2000.prime_minister,
    prime_minister_2000.ministry,
    prime_minister_2000.pm_recreation, and
    prime_minister_2000.pm_marriage.

    And, publish your expected results.

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

    In attached file you will find dump from mysql database and expected result is as follow:

    Code:
    +------------+------------------+------------------------------------+-------------+-------------+
    | pm_name    | party            | recreation                         | spouse_name | nr_children |
    +------------+------------------+------------------------------------+-------------+-------------+
    | Barton E   | Protectionist    | reading                            | Ross JM     |           6 |
    | Fadden A W | Country          | golf                               | Thornber I  |           4 |
    | Fraser J M | Liberal          | fishing, photography, vintage cars | Beggs T     |           4 |
    | Hughes W M | National labor   | golf, motoring, riding             | Cutts E     |           6 |
    | Hughes W M | Nationalist      | golf, motoring, riding             | Cutts E     |           6 |
    | Lyons J A  | United Australia | tennis                             | Burnell E M |          11 |
    | Page E C G | Country          | tennis                             | Blunt E     |           4 |
    +------------+------------------+------------------------------------+-------------+-------------+
    Cheers,
    Flybro
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT prime_minister_2000.prime_minister.pm_name
         , prime_minister_2000.ministry.party
         , prime_minister_2000.pm_recreation.recreation
         , prime_minister_2000.pm_marriage.spouse_name
         , prime_minister_2000.pm_marriage.nr_children
      FROM prime_minister_2000.prime_minister
    INNER 
      JOIN prime_minister_2000.ministry
        ON prime_minister_2000.ministry.pm_name = prime_minister_2000.prime_minister.pm_name
    INNER 
      JOIN ( SELECT pm_name
                  , GROUP_CONCAT(recreation) AS recreations
               FROM prime_minister_2000.pm_recreation
             GROUP
                 BY pm_name ) AS r
        ON r.pm_name = prime_minister_2000.ministry.pm_name 
    INNER
      JOIN prime_minister_2000.pm_marriage
        ON prime_minister_2000.pm_marriage.pm_name = = prime_minister_2000.ministry.pm_name
       AND prime_minister_2000.pm_marriage.nr_children >= 4
     WHERE prime_minister_2000.ministry.party <> 'Labor'
    ORDER 
        BY prime_minister_2000.ministry.pm_name ASC
         , prime_minister_2000.ministry.party ASC ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2012
    Location
    Narre Warren
    Posts
    10
    Thx mate, but after execution I got:

    Code:
    ERROR 1054 (42S22): Unknown column 'prime_minister_2000.pm_recreation.recreation' in 'field list'
    Cheers,
    Flybro

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, i forgot to change the column in the SELECT clause

    there was also a spurious additional equals sign

    try this now --
    Code:
    SELECT prime_minister_2000.prime_minister.pm_name
         , prime_minister_2000.ministry.party
         , r.recreations
         , prime_minister_2000.pm_marriage.spouse_name
         , prime_minister_2000.pm_marriage.nr_children
      FROM prime_minister_2000.prime_minister
    INNER 
      JOIN prime_minister_2000.ministry
        ON prime_minister_2000.ministry.pm_name = prime_minister_2000.prime_minister.pm_name
    INNER 
      JOIN ( SELECT pm_name
                  , GROUP_CONCAT(recreation) AS recreations
               FROM prime_minister_2000.pm_recreation
             GROUP
                 BY pm_name ) AS r
        ON r.pm_name = prime_minister_2000.ministry.pm_name 
    INNER
      JOIN prime_minister_2000.pm_marriage
        ON prime_minister_2000.pm_marriage.pm_name = prime_minister_2000.ministry.pm_name
       AND prime_minister_2000.pm_marriage.nr_children >= 4
     WHERE prime_minister_2000.ministry.party <> 'Labor'
    ORDER 
        BY prime_minister_2000.ministry.pm_name ASC
         , prime_minister_2000.ministry.party ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Flybro View Post
    Thx mate,

    In attached file you will find dump from mysql database and expected result is as follow:

    Code:
    +------------+------------------+------------------------------------+-------------+-------------+
    | pm_name    | party            | recreation                         | spouse_name | nr_children |
    +------------+------------------+------------------------------------+-------------+-------------+
    | Barton E   | Protectionist    | reading                            | Ross JM     |           6 |
    | Fadden A W | Country          | golf                               | Thornber I  |           4 |
    | Fraser J M | Liberal          | fishing, photography, vintage cars | Beggs T     |           4 |
    | Hughes W M | National labor   | golf, motoring, riding             | Cutts E     |           6 |
    | Hughes W M | Nationalist      | golf, motoring, riding             | Cutts E     |           6 |
    | Lyons J A  | United Australia | tennis                             | Burnell E M |          11 |
    | Page E C G | Country          | tennis                             | Blunt E     |           4 |
    +------------+------------------+------------------------------------+-------------+-------------+
    Cheers,
    Flybro
    Don't you want to concatenate parties, too?

    Like...
    Code:
    +------------+-----------------------------+------------------------------------+-------------+-------------+
    | pm_name    | party                       | recreation                         | spouse_name | nr_children |
    +------------+-----------------------------+------------------------------------+-------------+-------------+
    | Barton E   | Protectionist               | reading                            | Ross JM     |           6 |
    | Fadden A W | Country                     | golf                               | Thornber I  |           4 |
    | Fraser J M | Liberal                     | fishing, photography, vintage cars | Beggs T     |           4 |
    | Hughes W M | National labor, Nationalist | golf, motoring, riding             | Cutts E     |           6 |
    | Lyons J A  | United Australia            | tennis                             | Burnell E M |          11 |
    | Page E C G | Country                     | tennis                             | Blunt E     |           4 |
    +------------+-----------------------------+------------------------------------+-------------+-------------+

  8. #8
    Join Date
    May 2012
    Location
    Narre Warren
    Posts
    10
    Quote Originally Posted by r937 View Post
    try this now
    Thx mate, now is almost perfect with one exception, as you can see, pm_name is repeated too many times:

    Code:
    +------------+------------------+----------------------------------+-------------+-------------+
    | pm_name    | party            | recreations                      | spouse_name | nr_children |
    +------------+------------------+----------------------------------+-------------+-------------+
    | Barton E   | Protectionist    | reading                          | Ross JM     |           6 |
    | Fadden A W | Country          | golf                             | Thornber I  |           4 |
    | Fraser J M | Liberal          | fishing,photography,vintage cars | Beggs T     |           4 |
    | Fraser J M | Liberal          | fishing,photography,vintage cars | Beggs T     |           4 |
    | Fraser J M | Liberal          | fishing,photography,vintage cars | Beggs T     |           4 |
    | Fraser J M | Liberal          | fishing,photography,vintage cars | Beggs T     |           4 |
    | Fraser J M | Liberal          | fishing,photography,vintage cars | Beggs T     |           4 |
    | Hughes W M | National labor   | golf,motoring,riding             | Cutts E     |           6 |
    | Hughes W M | Nationalist      | golf,motoring,riding             | Cutts E     |           6 |
    | Hughes W M | Nationalist      | golf,motoring,riding             | Cutts E     |           6 |
    | Lyons J A  | United Australia | tennis                           | Burnell E M |          11 |
    | Lyons J A  | United Australia | tennis                           | Burnell E M |          11 |
    | Page E C G | Country          | tennis                           | Blunt E     |           4 |
    +------------+------------------+----------------------------------+-------------+-------------+

  9. #9
    Join Date
    May 2012
    Location
    Narre Warren
    Posts
    10
    Quote Originally Posted by tonkuma View Post
    Don't you want to concatenate parties, too?
    Thx mate,

    Sure, that would be nice.

    Cheers,
    Flybro

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Flybro View Post
    ... pm_name is repeated too many times:
    so what you do is concatenate the party names with a GROUP BY subquery, exactly the same way as the subquery for recreations

    what will get slightly more messy, though, is to concatenate spouses and children at the same time

    but you don't anticipate needing to do that, do you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2012
    Location
    Narre Warren
    Posts
    10
    Quote Originally Posted by r937 View Post
    so what you do is concatenate the party names with a GROUP BY subquery, exactly the same way as the subquery for recreations
    Well, I'm afraid I'm still too weak to do this by myself.

    Beside, looks like using SELECT DISTINCT make a result as expected but I'm not sure that is good method to do that in this way.
    Code:
    +------------+------------------+----------------------------------+-------------+-------------+
    | pm_name    | party            | recreations                      | spouse_name | nr_children |
    +------------+------------------+----------------------------------+-------------+-------------+
    | Barton E   | Protectionist    | reading                          | Ross JM     |           6 |
    | Fadden A W | Country          | golf                             | Thornber I  |           4 |
    | Fraser J M | Liberal          | fishing,photography,vintage cars | Beggs T     |           4 |
    | Hughes W M | National labor   | golf,motoring,riding             | Cutts E     |           6 |
    | Hughes W M | Nationalist      | golf,motoring,riding             | Cutts E     |           6 |
    | Lyons J A  | United Australia | tennis                           | Burnell E M |          11 |
    | Page E C G | Country          | tennis                           | Blunt E     |           4 |
    +------------+------------------+----------------------------------+-------------+-------------+
    Cheers,
    Flybro

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Flybro View Post
    Well, I'm afraid I'm still too weak to do this by myself.
    try

    see how i transformed the join to the recreation table into a join to a subquery on the recreation table

    just copy/paste that structure for the parties table, and make the changes as appropriate

    you can do it

    and if you get an error message, post what you tried
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Flybro View Post
    ...

    Beside, looks like using SELECT DISTINCT make a result as expected but I'm not sure that is good method to do that in this way.
    Code:
    +------------+------------------+----------------------------------+-------------+-------------+
    | pm_name    | party            | recreations                      | spouse_name | nr_children |
    +------------+------------------+----------------------------------+-------------+-------------+
    | Barton E   | Protectionist    | reading                          | Ross JM     |           6 |
    | Fadden A W | Country          | golf                             | Thornber I  |           4 |
    | Fraser J M | Liberal          | fishing,photography,vintage cars | Beggs T     |           4 |
    | Hughes W M | National labor   | golf,motoring,riding             | Cutts E     |           6 |
    | Hughes W M | Nationalist      | golf,motoring,riding             | Cutts E     |           6 |
    | Lyons J A  | United Australia | tennis                           | Burnell E M |          11 |
    | Page E C G | Country          | tennis                           | Blunt E     |           4 |
    +------------+------------------+----------------------------------+-------------+-------------+
    The redundancies in results must come from redundant data in prime_minister_2000.ministry.

    And you may want to specify SEPARATOR ', '.

    As a consequence, try something like...
    Code:
    SELECT pm.pm_name
         , p.parties
         , r.recreations
         , m.spouse_name
         , m.nr_children
      FROM prime_minister_2000.prime_minister AS pm
    INNER 
      JOIN ( SELECT pm_name
                  , GROUP_CONCAT(DISTINCT party SEPARATOR ', ') AS parties
               FROM prime_minister_2000.ministry
              WHERE prime_minister_2000.ministry.party <> 'Labor'
             GROUP
                 BY pm_name ) AS p
        ON p.pm_name = pm.pm_name
    INNER 
      JOIN ( SELECT pm_name
                  , GROUP_CONCAT(recreation SEPARATOR ', ') AS recreations
               FROM prime_minister_2000.pm_recreation
             GROUP
                 BY pm_name ) AS r
        ON r.pm_name = pm.pm_name 
    INNER
      JOIN prime_minister_2000.pm_marriage AS m
        ON m.pm_name = pm.pm_name
       AND m.nr_children >= 4
    ORDER 
        BY pm_name ASC

  14. #14
    Join Date
    May 2012
    Location
    Narre Warren
    Posts
    10
    Thank you guys, I really appreciate your patience and your help.

    Cheers,
    Flybro

Tags for this Thread

Posting Permissions

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