List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 19 2012 12:33am
Subject:SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
View as plain text  
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`),
)


Thread
SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding formatDaevid Vincent19 May
  • RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding formatDaevid Vincent19 May
    • Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding formatMark Kelly19 May
      • RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding formatDaevid Vincent20 May
    • Re: SQL query help. Retrieve all DVDs that have at least one scene ofa certain encoding formatBaron Schwartz20 May