Possible to do this in one query?

Databases
1

Hi!

I have three tables:

messages (ID, subject, timestamp);
messages_users (ID, mailid, userid);
messages_replies(ID,mailid,userid, message, timestamp);

What I want to do is display a list of their messages and the user’s who are involved in the conversation.

So for example I could do this in two queryies:

SELECT * FROM messages

then during the loop I could get the users

SELECT * FROM messages_users WHERE mailid = ?

loop through users and output

Is it possible to create one query for just this?

2

It doesn’t look like you have any fields that you could establish keys on. The two tables don’t have any common fields to match with.

In the second proposed query, where are you getting the “mailid” from? Do you have a field in “messages” called “mailid”?

If you do, then you can do a JOIN to solve this with one query.

3

Messages ID is the mailid in the second query (sorry about the confusion)

4

you’re going to also have to explain why the 3rd table you mentioned isn’t involved – or should it be?

why are message replies different from messages?

i think you ought to reconsider your design…

5

Well the third table is just the replies; it sort of works like threads with an access list - you have a “thread” which is the messages table, contains just the title etc. Then you have the posts for the thread which like message_replies. The access table determines who has access to this thread. I hope that makes a little more sense.

6

so basically we don’t need to look at the replies table at all, to determine which users are involved in the conversation, right?

7

Right! That’s just more of a security check to make sure the user viewing the message has access- but I also want to display to the user who has access without going to get another query for this (if possible).

8 
SELECT messages.* 
     , GROUP_CONCAT(messages_users.userid) AS users 
  FROM messages 
INNER
  JOIN messages_users
    ON messages_users.mailid = messages.id 
GROUP
    BY messages.id
9

Thanks this worked perfectly!