Use results from one sql query in another where statement (subquery?)
mysql, sql, subquery
Solution
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!)
Problem
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). Select all users who downloaded a specific file: ``` SELECT DISTINCT dmid FROM downloads_downloads where dfid = "7024" ``` Using the users above, find all the files they all downloaded: ``` SELECT dfid from downloads_downloads WHERE dmid = {user ids from #1 above} ``` 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") ```