SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    GROUP CONCAT with Table Joins

    Hi!

    I have:

    Table: location
    id, name
    1, ABC
    2, DEF
    3, GHI

    table: user
    id, username, name
    1, john, John
    2, peter, Peter
    3, mary, Mary

    table: reportusers
    fkLocationId, fkUserId
    2, 2
    2, 3

    I am trying to get this result:

    ABC, ""
    DEF, "Peter(peter), Mary (mary)"
    GHI, ""

    I have tried this SQL statement:

    SELECT GROUP_CONCAT(user.name, ' (', user.username ,')' SEPARATOR ', ') as names
    FROM location LEFT JOIN reportusers
    ON location.id = reportusers.fkLocationId
    LEFT JOIN user
    ON user.id = reportusers.fkUserId
    GROUP BY location.id

    but it only returns DEF, Peter(peter), Mary(mary) but not ABC and GHI.

    It is ok if I do not use GROUP_CONCAT.

    How do I get the desired result?

    Please help.

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by firblazer View Post
    but it only returns DEF, Peter(peter), Mary(mary) but not ABC and GHI.
    no, it doesn't return DEF either

    DEF comes from location.name, and location.name is not included in your SELECT clause at all

    Quote Originally Posted by firblazer View Post
    It is ok if I do not use GROUP_CONCAT.
    no, it is not okay, you have to use it if you want multiple users for each location

    Quote Originally Posted by firblazer View Post
    How do I get the desired result?
    i showed you how to use GROUP_CONCAT in your previous thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi!

    My apologise. My SQL as follows:

    SELECT location.name, GROUP_CONCAT(user.name, ' (', user.username ,')' SEPARATOR ', ') as names
    FROM location LEFT JOIN reportusers
    ON location.id = reportusers.fkLocationId
    LEFT JOIN user
    ON user.id = reportusers.fkUserId
    GROUP BY location.id

    Thanks for pointing out.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i believe your GROUP_CONCAT is still wrong

    please refer to my reply in your previous thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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