How to group NULL field

After this query :

SELECT forum_categories.id AS category_id, forum_categories.title, forum_topics.id AS topic_id, forum_topics.title开发者_如何学C, user
FROM forum_categories JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6
ORDER BY forum_categories.id

I got this results :

Now, I'd like to :

• add another field in the select, called thereIsANull
• group all my results by the field user category_id
• setting thereIsANull to 1 if there isn't a NULL in the field user (while grouping), 0 otherwise.

So in the example below, the result must be 1 row :

6    Welcome    (some topic_id)     (some title)   (djfonplaz or null)   0

and, if all user was different from NULL :

6    Welcome    (some topic_id)     (some title)   (djfonplaz)   1

How can I do it on MySql?

SELECT 
  forum_categories.id AS category_ids
  , forum_categories.title as titles
  , forum_topics.id AS topic_ids
  , forum_topics.title as topic_titles
  , count(*) as NumberOfRows
  , GROUP_CONCAT(IFNULL(user,'(no user)')) AS users 
  , (count(*) - count(user)) as IsThereANull  
FROM forum_categories 
INNER JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6
GROUP BY category_id
ORDER BY forum_categories.id

IsThereANull will be 0 is there's no null and 1 or more if there is.

I think that the following query should get the result that you are looking for:

SELECT forum_categories.id AS category_id, forum_categories.title, 
       forum_topics.id AS topic_id, forum_topics.title, user, 
       (
          SELECT MAX(IF(COUNT(*), 0, 1))
          FROM forum_categories JOIN forum_topics ON 
               forum_categories.id=forum_topics.category_id
          LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
          WHERE forum_categories.id=6 AND user IS NULL
    ) AS thereIsANull
FROM forum_categories JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6 AND user IS NOT NULL
GROUP BY user
ORDER BY forum_categories.id

I think something like this would do as you want:

SELECT cat.id AS category_id, 
       cat.title, 
       top.id AS topic_id, 
       top.title, 
       user,
       (
           (
               SELECT COUNT(*) FROM forum_categories 
               WHERE id = cat.id AND user IS NULL
           ) > 0 -- This is to check wether if any NULL is found
       ) as thereIsANull 
FROM forum_categories as cat
JOIN forum_topics as top ON cat.id = top.category_id
LEFT OUTER JOIN forum_views as view ON top.id = view.topic_id
WHERE cat.id = 6 -- You can ofcourse change this to whatever variable your using
GROUP BY cat.id -- Just an ordinary group by category id
ORDER BY cat.id

Assuming that the field user is found in the forum_categories table, other wise just modify the subquery to join that table where you get the user from

Note that Thiis is basically the same as the one Cez wrote, so he deserves some credits to, only difference is the subquery and grouping by category id instead of user.

Try it out and let me know if it was correct :)