Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to make a multi-table query that I am not quite sure how to do properly. I have User, Message, Thread, and Project.

A User is associated with Message/Thread/Project as either the Creator or as it being 'shared' with them.

A Message is contained within a Thread (associated by message.thread_id and, and a Thread is contained within a Project (associated by thread.project_id and project_id).

I would like to create a query where given a value, it will return all messages that the user has access to, as well as the Thread and Project name that that message is under, both as Creator or 'Shared'. I use a table to handle the 'shares'. The rough diagram is:

There are more columns in each, but I left them out for simplicity.

share|improve this question

looks like you need to use "join" syntax. Something like:

FROM User u
LEFT JOIN Message m ON m.owner_user_id =
LEFT JOIN Thread  t ON t.owner_user_id =
LEFT JOIN Project p ON p.owner_user_id =
WHERE = 12345

Of course, this will have to be modified to include the fields you actually want returned and whatever sorting and ordering you may want... but this should get you started. Good luck!

share|improve this answer

I would probably approach this one with a UNION.

Do a query for each type of message (message, project, thread):

SELECT, type, FROM (
  ( SELECT, 'project', project.* FROM ... // PROJECT OWNER AND SHARED QUERY // )
  ( SELECT, 'thread', thread.* FROM ... // THREAD OWNER AND SHARED QUERY // )
  ( SELECT, 'message', message.* FROM ... // MESSAGE OWNER AND SHARED QUERY // )
) combined

Where type is where the info came from, and contains the fields of the specific type. Just make sure your project.*, thread.*, message.* contains the same number of fields, otherwise UNION will complain.

**disclaimer, not tested, just a working theory.

share|improve this answer

The basic query should be as the following, replace 12345 with your user_id. You may add other fields as needed.

select message_id
from message msg
inner join (
    select thread_id 
    from thread thr
    inner join (
        select id as project_id from project where owner_user_id = 12345
        union select project_id from project_share where user_id = 12345
    ) prj on prj.project_id = a.project_id
    union select thread_id from thread_share where user_id = 12345
) thr on thr.thread_id = msg.group_id
union select message_id from message_share where user_id = 12345

Warning: not tested.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.