Select G.Id AS Id, G.GName AS GName, G.Photo AS Photo, G.UserName AS Owner,(select top 1 firstname from Members where UserName =G.UserName) as FirstName,(select top 1 LastName from Members where UserName =G.UserName) as LastName, (Select Count(*) From GroupMembers GM Where GM.GName = G.GName AND GM.Status = 1) AS Members, CONVERT(VARCHAR(11), gDate, 6) as gDate From groups G Where IsNull(G.Status, '1') = 1 AND G.CL <> '0' ; Select IsNull(CL, 0) AS CL, Count(*) AS Groups From Groups Where Status = 1 Group By CL
-- Wishbazaar