List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:May 20 2012 2:52am
Subject:Re: SQL query help. Retrieve all DVDs that have at least one scene of
a certain encoding format
View as plain text  
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.
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