SQL - must return 0 if GROUP_CONCAT value is null-CodePudding

I just want to not return any row if no result! I'm so null in sql that it depresses me, please help..

I have 2 tables with 1 association table :

shop table:

id	name	adress
1	name1	1 street..
2	name2	2 street..
3	name3	3 street..

activity table:

id	title
1	Distribution
2	Importation
3	Préparation
4	Production

shop_activity association table:

shop_id	activity_id
1	1
2	2
3	3
3	4

I found this request to concat activities:

    SELECT s.*, GROUP_CONCAT(a.title SEPARATOR ',') AS activities
    FROM shop s
    LEFT JOIN shop_activity sa
        ON s.id = sa.shop_id
    LEFT JOIN activity a
         ON sa.activity_id = a.id
    WHERE s.id = ?

and it does the job

id	name	adress	activities
3	name3	3 street..	Production, Importation

but if s.id doesn't exist in table shop, currently I have this result:

id	name	adress	activities
NULL	NULL	NULL	NULL

but I don't want any row if all is null to return a message, currently I make a condition on id column and I know that it isn't very clean!

CodePudding user response：

Try just join instead of LEFT:

SELECT s.*, GROUP_CONCAT(a.title SEPARATOR ',') AS activities
FROM shop s
JOIN shop_activity sa
    ON s.id = sa.shop_id
JOIN activity a
     ON sa.activity_id = a.id
WHERE s.id = ?

CodePudding user response：

I've found: just group by

SELECT s.*, GROUP_CONCAT(a.title SEPARATOR ',') AS activities
FROM shop s
LEFT JOIN shop_activity sa ON s.id = sa.shop_id
LEFT JOIN activity a ON sa.activity_id = a.id
WHERE s.id = ?
GROUP By s.id