CONCAT only returns rows that are not null

Question

I have searched in a few topics for how to resolve this issue I am having with a query that uses CONCAT but yet must return the groups even if the concat is null and I have found 2 solutions that doesnt work.

First solution was to use ISNULL:

#1582 - Incorrect parameter count in the call to native function 'ISNULL'

Second was to use IFNULL which works partially, it does find which are not null but yet doesnt print the null ones.

Here is the query:

  SELECT g.name, IFNULL(GROUP_CONCAT(c.name),'') AS commands
    FROM site_access b
    JOIN groups g ON b.group_id = g.id
    JOIN group_commands gc ON g.id = gc.group_id
    JOIN commands c ON gc.command_id = c.id
   WHERE b.site_id = 1
GROUP BY g.name
ORDER BY g.status ASC

I have 8 site_access, 8 groups and currently registered and only 2 commands that are assigned to group 1 and group 2, what currently happens is that it does print these 2 groups but ignore all the rest because they dont have commands.

Here is an example of the output:

Admin - add, del, announce
Member - find

Desired output:

Admin - add, del, announce
Member - find
Banned
OhterGroup1
OhterGroup2
OhterGroup3
OhterGroup4
OhterGroup5

if you need more information about the tables there is a sample of it in here: MySQL query for multiple tables being secondary tables multiple items?

Joe Stefanelli · Accepted Answer · 2011-06-24 21:24:03Z

2

Try using a LEFT JOIN to the groups_commands instead. Note how the JOIN to commands has also been moved in this query.

SELECT g.name, IFNULL(GROUP_CONCAT(c.name),'') AS commands
FROM site_access b
    INNER JOIN groups g 
        ON b.group_id = g.id
    LEFT JOIN group_commands gc
        INNER JOIN commands c 
            ON gc.command_id = c.id
        ON g.id = gc.group_id
WHERE b.site_id = 1
GROUP BY g.name
ORDER BY g.status ASC

answered Jun 24, 2011 at 21:24

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Prix Over a year ago

@Joe Stefanelli works just fine, that was one good example of what I was just commenting on the other question about inner and left joins ;) now it became quite simple to understand it

JoshL Over a year ago

Note that this with approach, the IFNULL is not necessary.

Joe Stefanelli Over a year ago

@JoshL: GROUP_CONCAT() will return NULL if no non-NULL values are found, so the IFNULL might still be desirable to convert those NULLs to empty strings.

Joe Stefanelli Over a year ago

@JoshL: Note that this question is tagged MySQL, not Oracle.

Collectives™ on Stack Overflow

CONCAT only returns rows that are not null

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related