Forum OpenACS Q&A: Response to Outer join Query for Users_files: Help

Collapse
Posted by Dan Wickstrom on
Ok, I didn't read your original post carefully. The query I posted wouldn't work if a user had more than one file in user_files. The correct way to do it with an outer join would be to do the following:

select u.user_id,
       u.last_name,
       uf.file_owner_flag
from users u 
       LEFT OUTER JOIN 
     (select owner_id, '*'::text as file_owner_flag 
        from users_files u
       where file_size > 0 
         and owner_id = u.owner_id) uf ON u.user_id = uf.owner_id;

You were correct about Todd's version being faster. I did some benchmarking on the two versions, and Todd's version was quite a bit faster. This was a misconception on my part, as I've always had the impression that a subselect in the target list would run slowly. Sorry about the misinformation.