> -----Original Message----- > Sent: Friday, May 18, 2012 5:34 PM > > I have a table of DVDs, another of scenes and a last one of encoding > formats/files... > > I want to find in one query all the dvd_id that have > 0 scene_id that's > encoded in format_id = 13. > In other words all DVDs that are format_id = 13 despite not having a direct > link. > > CREATE TABLE `dvds` ( > `dvd_id` smallint(6) unsigned NOT NULL auto_increment, > `dvd_title` varchar(64) NOT NULL default '', > `description` text NOT NULL, > PRIMARY KEY (`dvd_id`), > ) > > CREATE TABLE `scenes_list` ( > `scene_id` int(11) NOT NULL auto_increment, > `dvd_id` int(11) NOT NULL default '0', > `description` text NOT NULL, > PRIMARY KEY (`scene_id`), > ) > > CREATE TABLE `moviefiles` ( > `scene_id` int(11) NOT NULL default '0', > `format_id` int(3) NOT NULL default '0', > `filename` varchar(255), > `volume` smallint(6) NOT NULL default '0', > PRIMARY KEY (`scene_id`,`format_id`), > )
Actually, I may have figured it out. Is there a better way to do this? SELECT DISTINCT d.`dvd_id` AS `id`, (SELECT COUNT(s_sl.scene_id) AS s_tally FROM scenes_list AS s_sl JOIN moviefiles AS s_mf USING (scene_id) WHERE s_sl.dvd_id = d.`dvd_id` AND s_mf.format_id = 13) AS s_tally FROM `dvds` AS d WHERE d.`date_release` <= '2012-05-18' HAVING s_tally > 0 ORDER BY d.`date_release` DESC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql