Use results from one sql query in another where statement (subquery?)

Question

I see many similar questions but they're either so complex I can't understand them, or they don't seem to be asking the same thing.

It's simple: I have two columns: users (dmid) and downloads (dfid).

1. Select all users who downloaded a specific file:

   SELECT DISTINCT dmid FROM downloads_downloads where dfid = "7024" 

2. Using the users above, find all the files they all downloaded:

   SELECT dfid from downloads_downloads WHERE dmid = {user ids from #1 above} 

3. Count and order the dfid results , so we can see how many downloads each file received:

   dfid    dl_count_field ----    -------------- 18       103 3        77 903      66 

My attempt at answering.

This seems close, but MySql bogs down and doesn't respond even after 30 seconds--I restart Apache eventually. And I do not now how to structure the count and order by without getting syntax errors because of the complex statement--and it may not even be the right statement.

SELECT dfid from downloads_downloads WHERE dmid IN (     SELECT DISTINCT dmid FROM `downloads_downloads` where dfid = "7024") 

Answer 1

SELECT dfid,count(*)  from downloads_downloads  WHERE dmid IN (     SELECT dmid      FROM downloads_downloads      where dfid = "7024" ) group by dfid 

or using a self join

select t1.dfid,count(*) from downloads_downloads t1 inner join downloads_downloads t2 on t1.dmid = t2.dmid where t2.dfid = "7024" 

if this takes too long then you will probably need to post an explain plan (google it!)