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
RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
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
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
RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
-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