SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

[Daevid Vincent]

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`),
)



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql