Have a mysql query I'd like to be able to run with order by from second table

Question

SELECT * 
FROM app_detailsvvv as dtable 
WHERE primaryGenreName='".$ov['title']."'
  AND composed='1' AND 
  (SELECT COUNT(*) as c 
   FROM new_apps 
   WHERE trackId=dtable.trackId AND top>0) > 0 
ORDER BY top ASC

What I want to do here is to ORDER BY top of table new_apps not app_detailsvvv as it doesn't even have a top field, how to do that?

Answer 1

Use an INNER JOIN for this. This would be your query:

SELECT dtable.* FROM app_detailsvvv as dtable 
INNER JOIN new_apps ON new_apps.trackId=dtable.trackId
WHERE primaryGenreName='".$ov['title']."'
    AND composed='1' AND
    (SELECT COUNT(*) as c
    FROM new_apps
    WHERE trackId=dtable.trackId AND top>0) > 0
ORDER BY new_apps.top ASC