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
