Mysql – multi-table thesql query

MySQLquery

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.

Best Answer

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!

Related Solutions

Mysql – Help with complicated MySQL query

Have you considered using a Stored Function ?

You can split the union into your individual queries and if it passes any one of the three, return count(*). Otherwise, return 0

Some restrictions on stored functions

I recommended function over procedure because it's invoked like:

SELECT myfunc(userID, eventID);

and your query doesn't look like it needs a resultset returned, just a single value. If it needs a resultset, you would need a procedure (as noted in the restrictions documentation)

Mysql – Select row from one query where opposites are not present in second query

SELECT  m.id
    FROM  message AS m
    WHERE  NOT EXISTS 
      ( SELECT  *
            FROM  inbox AS i
            WHERE  i.message_id = m.id
              AND  i.to_user_id = m.from_user_id 
      )

I think it could also be cone with a LEFT JOIN (with similar efficiency), but NOT EXISTS more closely matches your description of the task.

Best Answer

Related Solutions

Mysql – Help with complicated MySQL query

Mysql – Select row from one query where opposites are not present in second query

Related Question