CodingForums.com - working with GROUP

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - working with GROUP_CONCAT error (http://www.codingforums.com/showthread.php?t=287053)

Kurisvo

02-04-2013 08:50 PM

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.

BubikolRamios

02-04-2013 09:21 PM

Quote:

LEFT JOIN

Quote:

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

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

Kurisvo

02-04-2013 09: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 09: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 09: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.

PHP Code:


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

Simple as that.

Kurisvo

02-04-2013 09:52 PM

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?

Old Pedant

02-04-2013 10: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:

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.

Kurisvo

02-04-2013 10:06 PM

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

Old Pedant

02-04-2013 10: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 10:12 PM

Quote:

Originally Posted by Kurisvo (Post 1310920)

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 10:18 PM

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

All times are GMT +1. The time now is 01:59 PM.