John Baum John Baum - 3 months ago 12
SQL Question

Combining selects depending on which table the result comes from

Lets say I have a db table called Document that contains rows for all the documents I own. I have another db table called Shared that has records for documents (owned by other people) that the specified user_id has access to. So if i were user1 for ex, I would have my documents in the Documents table and documents by user2 that I have access to stored in the Shared table with my user_id and user2's document_ids.



Each entry in the Documents table has permissions = "all". Each entry in the shared table has vaying levels of permissions such as {write, read, delete}. In my query to get back all documents owned/accessible for user foo, I would like to get back the proper permissions depending on whether the record was from Documents or Shared. I have a basic query now that pulls back all documents that i
own and have access to. My attempt is something like this:

Select, d.user_id, d.permissions
from Document d
left join Shared s on s.document_id =
where s.user_id = 1

Another attempt:

Select, d.user_id,
COALESCE(s.permissions, d.permissions) as permissions
from Documents d
left join Shared s on s.document_id = and s.user_id = 1;


If I understand correctly, I believe you just need to union the results from each table:

select * from 
    (select as document_id, d.user_id, 'all' as permissions,
    from t_documents d
    union all
    select s.document_id, s.user_id, s.permissions,
    from t_shared s
    left join t_documents dtl
    on s.document_id = tot
where tot.user_id = 1;