ok, here is the schema that I am working with: CREATE TABLE `cd` ( `id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL, `title` varchar(100) NOT NULL, `description` text NOT NULL, `dt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM
CREATE TABLE `song` ( `id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL, `title` varchar(50) NOT NULL, `artist` varchar(50) NULL, `album` varchar(50) NULL, `featuring` varchar(50) NULL, `length` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM CREATE TABLE `track` ( `id` int(10) unsigned NOT NULL auto_increment, `song_id` int(10) unsigned NOT NULL, `cd_id` int(10) unsigned NOT NULL, `track` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cd_id` (`cd_id`,`track`), UNIQUE KEY `song_id` (`song_id`,`cd_id`) ) ENGINE=MyISAM the query now, is pulling back all the cd table data, as well as the total length of the songs that are tracks on that CD, as well as the number of tracks. that query is working fine. my goal now, is to pull the same data, but only for CDs that contain a specific track. but if i add "where song.id = 'X' the the count only returns 1 and the sum only returns the length for that 1 song. I'd like the full length and track count, but only for CDs that contain a specific song. SELECT cd.*, count(track.track) as tracks, sum(song.length) as length from cd LEFT JOIN (track, song) on (track.cd_id = cd.id and track.song_id = song.id) GROUP BY cd.id ORDER BY dt DESC LIMIT 0,1 thanks,
