...

View Full Version : working with GROUP_CONCAT error



Kurisvo
02-04-2013, 09:50 PM
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
02-04-2013, 10:21 PM
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
02-04-2013, 10:40 PM
??? 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
02-04-2013, 10:40 PM
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
02-04-2013, 10:43 PM
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
02-04-2013, 10:50 PM
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.


$aList = array();
if (!empty($string))
{
$aList = explode(',', $string);
}

Simple as that.

Kurisvo
02-04-2013, 10:52 PM
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
02-04-2013, 11:05 PM
> 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
02-04-2013, 11:06 PM
thank you so much. So sorry I didn't add those...

Old Pedant
02-04-2013, 11:07 PM
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
02-04-2013, 11:12 PM
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
02-04-2013, 11:18 PM
Yep, that query fixed everything. Thank you very much, once again! Learn something new every day!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum