working with GROUP_CONCAT error

[Kurisvo]

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

First off, here is my query:

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.

[BubikolRamios]

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.

[Old Pedant]

??? 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:

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

[Kurisvo]

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.

[Old Pedant]

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???

[Fou-Lu]

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.

[Kurisvo]

I was replying to BubikolRamios

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

User | Items

So then with some info it would look like

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?

[Old Pedant]

> 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:

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.

[Kurisvo]

thank you so much. So sorry I didn't add those...

[Old Pedant]

The only exception to that rule is that you *can* make tests for IS NULL or IS NOT NULL in the WHERE clause.

[Old Pedant]

Originally Posted by Kurisvo

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.

[Kurisvo]

Yep, that query fixed everything. Thank you very much, once again! Learn something new every day!