niladri.primalink,

this one does not look right:

(SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status

TOP 1 with no ORDER BY means "random".

If there are 2 or more statuses for any particular file_id the result might be different every single time it's executed.

What do you need from this query?