Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 thread.id), 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 User.id 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: http://min.us/mvpqbAU

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

share|improve this question
add comment

3 Answers

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

SELECT 
u.name,
u.id,
m.id,
t.id,
p.id,
FROM User u
LEFT JOIN Message m ON m.owner_user_id = u.id
LEFT JOIN Thread  t ON t.owner_user_id = u.id
LEFT JOIN Project p ON p.owner_user_id = u.id
WHERE u.id = 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
add comment

I would probably approach this one with a UNION.

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

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

Where type is where the info came from, and type.info 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
add comment

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

select msg.id message_id
from message msg
inner join (
    select thr.id 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
add comment

Your Answer

 
discard

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