> -----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
