SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Possible to do this in one query?

    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. #2
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Check Out Syntaxxx.com - HTML5, CSS3, PHP, jQuery & More

  3. #3
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Messages ID is the mailid in the second query (sorry about the confusion)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Patche View Post
    What I want to do is display a list of their messages and the user's who are involved in the conversation.
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Patche View Post
    Well The access table determines who has access to this thread. I hope that makes a little more sense.
    so basically we don't need to look at the replies table at all, to determine which users are involved in the conversation, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Thanks this worked perfectly!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •