Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts

    working with GROUP_CONCAT error

    So I've been practicing with random queries and I've run into a problem

    First off, here is my query:

    Code:
    SELECT a.username,
                     GROUP_CONCAT(items.id) AS itemslist,
                    
             FROM a LEFT JOIN items ON items.owner = a.username          
             GROUP BY a.username
    and that works perfectly for what I need (I need the items in a list, like the group_concat function gives me), except when there are no records for that user in the items table. I'm wondering if you can help me write a query that would work both ways? So it would return the list of users from the a table, no matter if they have items or not.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    LEFT JOIN
    So it would return the list of users from the a table, no matter if they have items or not.
    left join does exactly that.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    ??? That works fine, even with no records in items for a given record in a.

    You will get NULL for the GROUP_CONCAT, of course.

    Is the problem than you don't know what to do with the NULL in your PHP code?

    If you want to provide a default value, in place of the NULL, just do this:
    Code:
    SELECT a.username,
           IFNULL( GROUP_CONCAT(items.id), '--none--' ) AS itemslist
    FROM a LEFT JOIN items ON items.owner = a.username          
    GROUP BY a.username
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Except a left join doesn't return the information in the format I need. Which is a list of the ids of the items with commas separating them. Such as 1,5,6,78.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Your statement makes no sense.

    If there are no matching items, then HOW CAN THERE POSSIBLY BE any list?

    If "john" has no matching records in the items table, then how can you expect to get a list of ids for "john" when there are no ids for him???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You need to choose what to do with it. You can either have a list of all a.username, and only matching itemslist within it (including NULL where no matching list exists), or no record of a.username if there is no matching itemslist.
    I can't see what you are doing with a comma separating list that cannot be accommodated for using null instead. Personally I wouldn't use a group_concat, but that's just me. With the comma string, and a language like PHP, you'd execute an explode() call and it will return an array of each item. Problem is empty strings and nulls are considered a single character (that is, null), which can be exploded resulting in a single item of empty string.
    PHP Code:
    $aList = array();
    if (!empty(
    $string))
    {
        
    $aList explode(','$string);

    Simple as that.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I was replying to BubikolRamios

    Basically what I'm trying to do is populate a (html) table like this:

    Code:
    User | Items
    So then with some info it would look like

    Code:
    User | Items
            ___________
            John   | 1,5,6
            James | 5,7,8,1
            Megan | None
            Tim    | 5,2,3
    However when I run the query, Megan isn't returned because she doesn't have any item records. Would the WHERE statements I have limiting what kind of items being returned mess it up?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    > Would the WHERE statements I have limiting what kind of items being returned mess it up?

    YES!!!

    You can *NOT* use WHERE with the *dependent* table! (That is, the right side table in a LEFT join. It would be the left side table in a RIGHT join, of course.)

    If you do, you CONVERT the LEFT JOIN (or RIGHT JOIN) to an INNER JOIN!!!!

    Any conditions that you want to put on the dependent table *MUST* be part of the ON condition and *NOT* be in the WHERE!

    Let's say you currently have WHERE items.size > 73

    So move that to the ON, thus:
    Code:
    SELECT a.username,
           IFNULL( GROUP_CONCAT(items.id), '--none--' ) AS itemslist
    FROM a LEFT JOIN items 
    ON items.owner = a.username AND items.size > 73
    GROUP BY a.username
    Next time, show us the entire query and we'll be able to help you better.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Kurisvo (02-04-2013)

  • #9
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    thank you so much. So sorry I didn't add those...

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    The only exception to that rule is that you *can* make tests for IS NULL or IS NOT NULL in the WHERE clause.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Quote Originally Posted by Kurisvo View Post
    thank you so much. So sorry I didn't add those...
    Hey, I give you a lot of credit for asking the right question about the WHERE clause. I can't tell you how many times I have run into this where the coder had no clue at all. You have the right instincts! Congratulations.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Yep, that query fixed everything. Thank you very much, once again! Learn something new every day!


  •  

    Posting Permissions

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