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

2012-05-19 Thread Mark Kelly 
Hi.

On Friday 18 May 2012 18:21:07 Daevid Vincent wrote:

 Actually, I may have figured it out. Is there a better way to do this?

I don't see why you need the dvds table when the dvd_id is in the scene table:

SELECT a.dvd_id 
FROM scenes_list a, moviefiles b 
WHERE a.scene_id = b.scene_id 
AND b.format_id = '13';

or am I misunderstanding something?

Cheers,

Mark


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

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

2012-05-19 Thread Daevid Vincent 
There are a bunch of other columns in all these tables. A quick reason is
need the dvd.title too therefore the dvd table is needed. Another reason is
that the query is generated programmatically based upon parameters passed to
a method. But yes, I do she your point and maybe I can refactor some things
in this special case.

I haven't tried your query as I'm home and not at work right ATM, but I
think you need a DISTINCT dvd_id right? Otherwise I'll get a bunch of rows
all with the same dvd_id since multiple scene_ids will match.

d

-Original Message-
From: Mark Kelly [mailto:my...@wastedtimes.net] 
Sent: Saturday, May 19, 2012 3:34 PM
To: mysql@lists.mysql.com
Subject: Re: SQL query help. Retrieve all DVDs that have at least one scene
of a certain encoding format

Hi.

On Friday 18 May 2012 18:21:07 Daevid Vincent wrote:

 Actually, I may have figured it out. Is there a better way to do this?

I don't see why you need the dvds table when the dvd_id is in the scene
table:

SELECT a.dvd_id 
FROM scenes_list a, moviefiles b 
WHERE a.scene_id = b.scene_id 
AND b.format_id = '13';

or am I misunderstanding something?

Cheers,

Mark


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


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

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

2012-05-19 Thread Baron Schwartz 
I would work from the inside out. What you're doing is grouping scenes
by DVD and throwing away the ones that have no scenes. If you start
with DVDs and do a subquery for each row, you'll process DVDs without
scenes and then filter them out. If you start with a subquery that's
grouped by DVD ID, alias it with an AS clause, and then join from that
into the other tables, you can avoid that. It requires a little
backwards-thinking but it tends to work well in a lot of cases.  It
would look something like this. Here's the query against the scenes:

select dvd_id, count(*) as cnt from scenes_list group by dvd_id having
count(*)  0;

Now you can put that into a subquery and join to it:

select ...
from (
  copy/paste the above
) as s_sl
inner join dvds using (dvd_id)
rest of query;

I'm taking shortcuts because you said there is more to this query than
you've shown us, so I won't spend the time to make it a complete
query.

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

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

2012-05-18 Thread 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

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

2012-05-18 Thread Daevid Vincent 
 -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 
WHEREd.`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