CODEWITHSUNDEEP
mysql
Hobbyist
Hobbyist

Reputation: 16212

How to call NOT NULL on GROUP_CONCAT

Here's an example query:

SELECT u.user_id, 
    (SELECT GROUP_CONCAT(photo_id) 
        FROM photos p WHERE p.user_id = u.user_id) 
    AS photo_ids FROM users u;

Now, lets say for example purposes we have these tables:

Users:

+---------+-----------------------+
| user_id |         email         |
+---------+-----------------------+
|       1 | [email protected] |
|       2 | [email protected]     |
+---------+-----------------------+

Photos:

+---------+----------+
| user_id | photo_id |
+---------+----------+
|       1 |        1 |
|       1 |        2 |
+---------+----------+

The results of the query above would form these results:

+---------+-----------+
| user_id | photo_ids |
+---------+-----------+
|       1 | 1,2       |
|       2 | NULL      |
+---------+-----------+

How can I make it so that if the photo_ids column is NULL that it isn't returned?

I've tried:

SELECT u.user_id, 
    (SELECT GROUP_CONCAT(photo_id) 
        FROM photos p WHERE p.user_id = u.user_id) 
    AS photo_ids FROM users u
WHERE photo_ids IS NOT NULL;

but this returns a syntax error:

#1054 - Unknown column 'photo_ids' in 'where clause'

I've also tried HAVING as I've read it works with group_concat fields.

SELECT u.user_id, 
    (SELECT GROUP_CONCAT(photo_id) 
        FROM photos p WHERE p.user_id = u.user_id) 
    AS photo_ids FROM users u
HAVING photo_ids IS NOT NULL;

RESULT: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING photo_ids IS NOT NULL' at line 1

Upvotes: 0

Views: 610

Answers (3)

Manoj Singh
Manoj Singh

Reputation: 3

Try this MySQL query :- 

SELECT u.user_id, 
    (SELECT GROUP_CONCAT(photo_id) 
        FROM photos p WHERE p.user_id = u.user_id) 
    AS photo_ids FROM users u
WHERE photo_ids IS NOT NULL;

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try right join :-

select Users.user_id,GROUP_CONCAT(photo_id) as photo_ids 
from Users right join Photos 
on Users.user_id=Photos.user_id group by Users.user_id

Run query

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

You can use derived table in mysql and filter out to select only NOT NULL rows.

select * from
(
SELECT u.user_id, 
    (SELECT GROUP_CONCAT(photo_id) 
        FROM photos p WHERE p.user_id = u.user_id)  
    AS photo_ids FROM users u
) as DT
where DT.photo_ids not null;

Live Demo

Upvotes: 1

Related Questions