Code:
SELECT s.URLName
       , s.SubCategoryID
       , s.SubCategoryName
       , COUNT(DISTINCT(e.EventID)) AS COUNT 
       FROM subeventcategory s
		  LEFT JOIN 
                 eventcategory c ON c.CategoryID  = s.CategoryID
		  LEFT JOIN 
                 event e ON s.SubCategoryID = e.SubCategoryID
          WHERE e.IsActive =1  AND c.URLName = 'music'
              AND (DATE(e.ToDate) >= DATE(NOW()) OR e.ToDate = "0000-00-00" )
          GROUP BY s.SubCategoryID 
          ORDER BY s.SubCategoryName ASC;
I need the code above to return no of event counts per subcategory even for sucategories with event count = 0

At the moment this query ONLY returns subcategories with eventcount > 0

If i change the WHERE clause to AND it returns all the subcategories in the db with the respective event counts.

Here is a dump of of the data needed for any tests. Thanks in advance!

EVENT MAIN CATEGORY:

Code:
-- Table structure for table `eventcategory`
--

CREATE TABLE IF NOT EXISTS `eventcategory` (
  `CategoryID` int(10) unsigned NOT NULL auto_increment,
  `CategoryName` varchar(40) NOT NULL,
  `URLName` varchar(200) NOT NULL,
  PRIMARY KEY  (`CategoryID`),
  UNIQUE KEY `Index_2` (`URLName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=15 ;

--
-- Dumping data for table `eventcategory`
--

INSERT INTO `eventcategory` (`CategoryID`, `CategoryName`, `URLName`) VALUES
(1, 'Music', 'music'),
(2, 'Happy Hour', 'happy-hour'),
(3, 'Food and Drink', 'food-and-drink'),
(4, 'Arts/Theatre/Film', 'arts-theatre-film'),
(5, 'Sport/Health', 'sport-health'),
(6, 'Science/Tech/Education', 'science-tech-education'),
(7, 'Nightlife', 'nightlife'),
(8, 'Family', 'family'),
(9, 'Organizations/Charities', 'organizations-charities'),
(10, 'Business/Politics ', 'business-politics'),
(11, 'Other', 'other'),
(12, 'Gay and Lesbian', 'gay-and-lesbian'),
(13, 'Second Saturday', 'second-saturday'),
(14, 'Holidays', 'holidays');
EVENT SUBCATEGORY

Code:
-- Table structure for table `subeventcategory`
--

CREATE TABLE IF NOT EXISTS `subeventcategory` (
  `SubCategoryID` int(10) unsigned NOT NULL auto_increment,
  `SubCategoryName` varchar(45) NOT NULL,
  `CategoryID` int(10) unsigned NOT NULL,
  `URLName` varchar(200) NOT NULL,
  PRIMARY KEY  (`SubCategoryID`),
  UNIQUE KEY `Index_3` (`URLName`),
  KEY `FK_subeventcategory_1` (`CategoryID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=111 ;

--
-- Dumping data for table `subeventcategory`
--

INSERT INTO `subeventcategory` (`SubCategoryID`, `SubCategoryName`, `CategoryID`, `URLName`) VALUES
(1, 'Alternative', 1, 'alternative'),
(2, 'Classical', 1, 'classical'),
(3, 'Comedy', 1, 'comedy'),
(4, 'Country/Folk', 1, 'country-folk'),
(5, 'Dance/Electronic', 1, 'dance-electronic'),
(6, 'Hard Rock/Metal ', 1, 'hardrock-and-metal'),
(7, 'Jazz/Blues', 1, 'jazz-and-blues'),
(8, 'Latin', 1, 'latin'),
(9, 'New Age/Spiritual ', 1, 'newage-spiritual'),
(10, 'Rap and Hip-Hop ', 1, 'rap-and-hiphop'),
(11, 'R&B/Urban Soul', 1, 'r-and-b-urban-soul'),
(12, 'Rock/Pop', 1, 'rock-and-pop'),
(13, 'World', 1, 'world'),
(14, 'Other - Music', 1, 'other-music'),
(15, 'Monday', 2, 'monday'),
(16, 'Tuesday', 2, 'tuesday'),
(17, 'Wednesday', 2, 'wednesday'),
(18, 'Thursday', 2, 'thursday'),
(19, 'Friday', 2, 'friday'),
(20, 'Saturday', 2, 'saturday'),
(21, 'Sunday', 2, 'sunday'),
(23, 'Wine Tasting', 3, 'wine-tasting'),
(24, 'Specials', 3, 'specials'),
(25, 'Festivals', 3, 'festivals'),
(26, 'Farmers Market', 3, 'farmers-market'),
(27, 'Other - Food and Drink', 3, 'other-food-and-drink'),
(28, 'Film', 4, 'film'),
(29, 'Galleries', 4, 'galleries'),
(30, 'Ballet and Dance', 4, 'ballet-and-dance'),
(31, 'Literary', 4, 'literary'),
(32, 'Broadway', 4, 'broadway'),
(33, 'Visual Arts', 4, 'visual-arts'),
(34, 'Classical Music', 4, 'classical-music'),
(35, 'Off Broadway', 4, 'off-broadway'),
(36, 'Museums/Exhibits', 4, 'musuems-and-exhibits'),
(37, 'Musicals', 4, 'musicals'),
(38, 'Opera', 4, 'opera'),
(39, 'Plays', 4, 'plays'),
(40, 'Other - Arts/Theatre/Film', 4, 'other-art-theatre-film'),
(41, 'Baseball', 5, 'baseball'),
(42, 'Basketball', 5, 'basketball'),
(43, 'Boxing', 5, 'boxing'),
(44, 'Competitions', 5, 'competitions'),
(45, 'Curling', 5, 'curling'),
(46, 'Field Sports', 5, 'field-sports'),
(47, 'Football', 5, 'football'),
(48, 'Golf', 5, 'golf'),
(49, 'Hockey', 5, 'hockey'),
(50, 'Lacrosse', 5, 'lacrosse'),
(51, 'Mixed Martial/Arts', 5, 'mixed-maritials-arts'),
(52, 'Motorsports', 5, 'motor-sports'),
(53, 'Rodeo/Bull Riding', 5, 'rodeo-bull-riding'),
(54, 'Skating', 5, 'skating'),
(55, 'Soccer', 5, 'soccer'),
(56, 'Tennis', 5, 'tennis'),
(57, 'Volleyball', 5, 'volleyball'),
(58, 'Wrestling', 5, 'wrestling'),
(59, 'MLB', 5, 'mlb'),
(60, 'NFL', 5, 'nfl'),
(61, 'NHL', 5, 'nhl'),
(62, 'NBA', 5, 'nba'),
(63, 'WNBA', 5, 'wnba'),
(64, 'Yoga', 5, 'yoga'),
(65, 'Other - Sports/Health', 5, 'other-sports-health'),
(66, 'Night Club', 7, 'night-club'),
(67, 'Latin Dance', 7, 'latin-dance'),
(68, 'Dance Lessons', 7, 'dance-lessons'),
(69, 'Adult', 7, 'adult'),
(70, 'Other - Nightlife', 7, 'other-nightlife'),
(71, 'Children''s Music and Theater', 8, 'childrens-music-and-theater'),
(72, 'Learning', 8, 'learning'),
(73, 'Circus', 8, 'circus'),
(74, 'Fairs/Festivals', 8, 'fairs-and-festivals'),
(75, 'Family Attractions', 8, 'family-attractions'),
(76, 'Ice Shows', 8, 'ice-shows'),
(77, 'Magic Shows', 8, 'magic-shows'),
(78, 'Other - Family', 8, 'other-family'),
(79, 'Fundraisers', 9, 'fundraisers'),
(80, 'Religious', 9, 'religious'),
(81, 'Non Profit', 9, 'non-profit'),
(82, 'Lectures', 9, 'lectures-organizations-charities'),
(83, 'Other - Organizations/Charities', 9, 'other-organizations-charities'),
(84, 'Conferences ', 10, 'conferences'),
(85, 'General Business', 10, 'general-business'),
(86, 'Lectures', 10, 'lectures-business-politics'),
(87, 'Speeches', 10, 'speeches'),
(88, 'Launches', 10, 'launches'),
(89, 'General Political', 10, 'general-political'),
(90, 'Other - Business/Politics', 10, 'other-business-politics'),
(91, 'Science', 6, 'science'),
(92, 'Technology', 6, 'technology'),
(93, 'Computers', 6, 'computers'),
(94, 'Education', 6, 'education'),
(95, 'Other - Science/Tech/Education', 6, 'other-science-tech-education'),
(96, 'Gay and Lesbian', 12, 'gay-and-lesbian'),
(97, 'Second Saturday', 13, 'second-saturday'),
(98, 'Mondays', 7, 'mondays'),
(99, 'Tuesdays', 7, 'tuesdays'),
(100, 'Wednesdays', 7, 'wednesdays'),
(101, 'Thursdays', 7, 'thursdays'),
(102, 'Fridays', 7, 'fridays'),
(103, 'Saturdays', 7, 'saturdays'),
(104, 'Sundays', 7, 'sundays'),
(105, 'Thanksgiving', 14, 'thanksgiving'),
(106, 'Christmas', 14, 'christmas'),
(107, 'New Years Eve', 14, 'new-years-eve'),
(108, 'New Years Day', 14, 'new-years-day'),
(109, 'Mo-Fr', 2, 'mo-fr'),
(110, 'Weekdays', 2, 'weekdays');
EVENTS TABLE

Code:
-- Table structure for table `event`
--

CREATE TABLE IF NOT EXISTS `event` (
  `EventID` int(10) unsigned NOT NULL auto_increment,
  `EventName` varchar(35) NOT NULL,
  `FromDate` date NOT NULL default '0000-00-00',
  `ToDate` date NOT NULL default '0000-00-00',
  `EventTime` time NOT NULL,
  `Description` text NOT NULL,
  `MainThumbnail` varchar(100) NOT NULL,
  `CategoryID` int(10) unsigned NOT NULL,
  `Cover` varchar(6) default '$0',
  `AreaID` int(10) unsigned NOT NULL,
  `DateAdded` datetime NOT NULL,
  `IsActive` tinyint(1) NOT NULL default '0',
  `Type` varchar(8) NOT NULL,
  `AgeRestriction` varchar(10) default NULL,
  `URLName` varchar(200) NOT NULL,
  `ShortDescription` varchar(150) NOT NULL,
  `DetailsThumbnail` varchar(100) NOT NULL,
  `SubCategoryID` int(10) unsigned NOT NULL,
  `EventDay` varchar(45) NOT NULL,
  `Gay` tinyint(1) unsigned default '0',
  PRIMARY KEY  (`EventID`),
  UNIQUE KEY `Index_5` (`URLName`),
  KEY `FK_event_1` (`CategoryID`),
  KEY `FK_event_2` (`AreaID`),
  KEY `FK_event_3` (`SubCategoryID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 3072 kB; (`AreaID`) REFER `dbsac/area`(`AreaID`' AUTO_INCREMENT=16 ;

--
-- Dumping data for table `event`
--

INSERT INTO `event` (`EventID`, `EventName`, `FromDate`, `ToDate`, `EventTime`, `Description`, `MainThumbnail`, `CategoryID`, `Cover`, `AreaID`, `DateAdded`, `IsActive`, `Type`, `AgeRestriction`, `URLName`, `ShortDescription`, `DetailsThumbnail`, `SubCategoryID`, `EventDay`, `Gay`) VALUES
(1, 'Decorex 2009', '2009-11-03', '2009-11-03', '07:50:49', 'Pellentesque elementum commodo ante. Mauris sollicitudin, nibh at tempus blandit, dui sem placerat lorem, sit amet facilisis ligula justo in neque. Sed dignissim malesuada erat. Maecenas at diam in magna tincidunt fringilla. In hac habitasse platea dictumst. Ut dignissim odio a turpis. Cras elementum justo a massa. Nullam eu odio. Aenean lectus sem, lacinia in, euismod sed, vulputate a, pede. Pellentesque lectus mi, fringilla vel, suscipit nec, convallis in, sem. ', 'events/event168112/main_thumbnail/thumb_event1680211.jpg', 1, '$16', 11, '2008-09-14 19:57:58', 1, 'onceoff', '18+', 'decorex-2009', 'Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Vivamus congue pede vel massa. Nullam viverra justo accumsan dia', 'events/event168112/details_thumbnail/5.jpg', 3, '', 0),
(2, 'J&B Crackdown', '2009-11-29', '2009-11-29', '11:50:49', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Mauris libero. Praesent lobortis, tellus ac placerat viverra, magna felis cursus nibh, et rhoncus turpis turpis eget orci. Cras et enim consectetuer sem faucibus laoreet. Nullam leo. Duis tincidunt dictum purus. Quisque vitae purus venenatis mauris ullamcorper rhoncus. Pellentesque id urna at lorem suscipit tempor. Donec nibh diam, congue et, consequat eget, bibendum sed, odio. In aliquet magna at mi. Nunc feugiat sapien eget est. Vivamus interdum pulvinar neque. Integer elementum.', 'events/event168454/main_thumbnail/thumb_event1680211.jpg', 5, '$10', 5, '2009-10-24 11:11:49', 1, 'onceoff', 'All Ages', 'jb-crackdown', 'Fusce volutpat molestie nisi. Vestibulum nibh lectus, congue et, sodales eu, facilisis quis, quam. Ut viverra tempus nibh.', 'events/event168454/details_thumbnail/3.jpg', 48, '', 0),
(3, 'ANC Annual Meeting', '0000-00-00', '0000-00-00', '11:30:49', 'Suspendisse potenti. Phasellus ante eros, vestibulum rutrum, aliquet interdum, tristique aliquam, ante. Proin molestie ipsum sed sem. Sed eu nulla. Sed at arcu quis massa pulvinar aliquet. Suspendisse dolor justo, tempus sed, consequat quis, sollicitudin ut, sem. Aliquam rhoncus accumsan leo. Pellentesque felis magna, auctor quis, posuere nec, facilisis non, ipsum. Praesent risus nunc, dictum et, tempor eget, lacinia a, nisl. Nam quis arcu cursus enim mattis iaculis.', 'events/event1680007/main_thumbnail/thumb_event1680211.jpg', 2, '$0', 89, '2009-12-14 21:06:12', 1, 'weekly', '21+', 'anc-annual-meeting', 'Etiam bibendum erat vel ipsum. Nullam mattis. Ut enim diam, blandit sit amet, venenatis volutpat, suscipit fermentum', 'events/event1680007/details_thumbnail/2.jpg', 16, 'Mon, Tues & Fri Nights & weds', 0),
(5, 'Ackermans Summit', '0000-00-00', '0000-00-00', '00:00:00', 'Cras massa dolor, rhoncus et, ullamcorper vel, elementum quis, dolor. Ut elit quam, semper eget, ornare eget, vehicula eget, arcu. Ut sed massa ac felis consectetuer dictum. Phasellus eu orci. Maecenas sit amet dui. Nulla sapien nisi, ultrices vitae, tempus ac, ultrices eu, sapien. Vivamus porta viverra metus. Phasellus cursus sollicitudin dui. Duis interdum nisl feugiat enim. Curabitur elit urna, semper ac, faucibus at, viverra ut, orci. Quisque posuere', 'events/event1680219/main_thumbnail/thumb_event1680211.jpg', 2, '$50.78', 74, '2008-09-13 08:48:12', 1, 'weekly', 'All Ages', 'ackermans-summit', 'Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Vivamus congue pede vel massa. Nullam viverra justo accumsan dia', 'events/event1680219/details_thumbnail/1.jpg', 17, 'fir and monday nights', NULL),
(15, 'Live Karoake', '2010-03-16', '2010-09-18', '18:10:00', 'kjk', 'events/event151003/main_thumbnail/thumb_event151003.jpg', 7, '$50', 14, '2009-10-03 05:34:45', 1, 'onceoff', '21+', 'anc-annual-meetings', 'ukjkjk', 'events/event151003/details_thumbnail/det_event151003.jpg', 100, '', NULL);

--