When doing a UNION in mysql how can I do a where on the results

Question

Hi I am doing a union over several tables. It's a little long but works!

(SELECT user_id,added_date,group_id,'joined',0,0,'' FROM group_members WHERE status = 1)
UNION
(SELECT user_id,added_date,object_id,'made a comment',0,0,'' FROM comments WHERE object_type = 11 AND status = 1)
UNION
(SELECT user_id,added_date,group_id,'made the event',1,group_calendar_id,title FROM group_calendars WHERE status = 1)
UNION
(SELECT comments.user_id,comments.added_date,group_calendars.group_id,'made a comment on the event',1,group_calendar_id,'' FROM group_calendars 
        INNER JOIN comments ON group_calendars.group_calendar_id = comments.object_id WHERE group_calendars.status = 1 AND comments.status = 1 AND object_type = 10 
)
UNION
(SELECT user_id,pd.added_date,pd.object_id,'uploaded a photo',2,pd.photo_data_id,
    (SELECT varchar_val FROM photo_data WHERE data_id = 1 AND photo_data.photo_id = photos.photos_id AND object_type = 3 AND object_id = pd.object_id) 
    FROM photo_data pd 
        INNER JOIN photos ON photos.photos_id = pd.photo_id 
    WHERE photos.photo_status = 1 AND pd.status = 1 AND pd.data_id = 0 AND pd.object_type = 3
 )

UNION
(SELECT cp.user_id,cp.added_date,cp.object_id,'made a comment on the photo',2,pd.photo_data_id,
(SELECT varchar_val FROM photo_data WHERE data_id = 1 AND photo_data.photo_id = photos.photos_id AND object_type = 3 AND object_id = pd.object_id) 
 FROM comments cp 
    INNER JOIN photo_data pd ON pd.photo_data_id = cp.object_id
    INNER JOIN photos ON photos.photos_id = pd.photo_id 
 WHERE cp.object_type = 8 AND cp.status = 1 AND pd.status = 1 AND pd.data_id = 0 AND photos.photo_status = 1 AND pd.object_type = 3 
)
UNION
(SELECT user_id,added_date,group_id,'made a topic',3,forum_topic_id,title FROM forum_topics WHERE forum_categories_id = ".GROUP_FORUM_CATEGORY." AND group_id > 0 AND status = 1)
UNION
(SELECT forum_comments.user_id,forum_comments.added_date,group_id,'made a comment on the topic',3,forum_comments.forum_topic_id,title FROM forum_comments 
INNER JOIN forum_topics ON forum_comments.forum_topic_id = forum_topics.forum_topic_id 
WHERE forum_topics.forum_categories_id = 16 AND forum_topics.group_id > 0 AND forum_topics.status = 1 AND forum_comments.status = 1
)

This gets all the activity from a set of groups. My question is at the end I want to make sure that the group is active.

So at the end want to do something like WHERE (SELECT COUNT(1) FROM groups g WHERE g.group_id = group_id AND status = 1) = 1

Is there any way of doing that?

Answer 1

i'd suggest to store it to a view or temporary table and query the view then. i know you will have two calls then, but it's actually faster in mysql that way.