CREATE PROCEDURE [dbo].[spSelectMembers]
AS
BEGIN
	SET NOCOUNT ON;
	select
		profile_id,
		profile_username,
		profile_avatar,
		(case isnull(pic.picture_cnt, 0) when 0 then 0 else pic.picture_cnt end) as picture_cnt,
		(case isnull(vdo.video_cnt, 0) when 0 then 0 else vdo.video_cnt end) as video_cnt,
		(case isnull(fp.post_cnt, 0) when 0 then 0 else fp.post_cnt end) as post_cnt,
		(case isnull(sta.online, 0) when 0 then 'offline' else 'online' end) as user_online
	from
		tblUserProfile as up
	left join
	(
		select
			picture_uploader, count(*) as picture_cnt
		from
			tblPictures, tblUserProfile
		where
			tblPictures.picture_uploader = tblUserProfile.profile_id
		group by
			picture_uploader
	) as pic
	on
		pic.picture_uploader = up.profile_id
	left join
	(
		select
			video_uploader, count(*) as video_cnt
		from
			tblVideos, tblUserProfile
		where
			tblVideos.video_uploader = tblUserProfile.profile_id
		group by
			video_uploader
	) as vdo
	on
		vdo.video_uploader = up.profile_id
	left join
	(
		select
			poster_name, count(*) as post_cnt
		from
			tblForumPosts, tblUserProfile
		where
			tblForumPosts.poster_name = tblUserProfile.profile_username
		group by
			poster_name
	) as fp
	on
		fp.poster_name = up.profile_username
	left join
	(
		select
			username, count(*) as online
		from
			tblUsersOnline, tblUserProfile
		where
			tblUsersOnline.username = tblUserProfile.profile_username
		group by
			username
	) as sta
	on
		sta.username = up.profile_username
END