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

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