mysql select和几个表的位置（非常棘手）

Question

I have 4 Tables (listed bellow) and need:

1. get last 10 Chats from Room 3 without banned users
2. show nickname for fromuserid
3. HIDE Users $userid dont like to see table "HIDE"

Table 1 "chats"

ID(autoinc)   fromuserid   roomid   text
 1               23          3      bla
 2               14          1      bla
 3               11          3      bal

Table 2 "user" /shorted/

ID(autoinc)   nickname   banned
1             chris        0
2             paul         1     // 1 = banned

Table 3 "hide"

ID(autoinc)   orguser    hideuser
1             12          3
2             33          12

Right now i solved it with PHP Routine, but I have to go through EACH result and make always a new query, that needs too long;

  $userid = 1; // actual user

  // List all chats and show userid as nickname
  $sql_com = "SELECT user.id, user.nickname, chats.text, chats.id ".
        " FROM chats, user".
        " WHERE ".
        " chats.fromuserid = user.id ".
        " AND chats.roomid = 3 ".
        " AND user.banned != 1 ".
        " ORDER BY chats.id DESC";  
  $result = mysql_query ($sql_com);       

  $count = 0;
  while ($row = mysql_fetch_array($result, MYSQL_NUM)) 
   {
       $dontshow = false;

       // Filter : dont show users $userid dont like to see (table "hide")
       $sql_com2 = "SELECT id from hide WHERE ( (orguser = ".$userid.") AND (hideuser = ".$row[0].") ) ";

       if ($result2 = mysql_query ($sql_com2)) 
       {
          if (mysql_num_rows($result2) > 0) $dontshow = true;
       }      


       // Output     
       if ($dontshow == false)
       {
            $count++;
            echo "Nickname: ".$row[1]." Text: ".$row[2];
       }

       if ($count > 10) break;
}

Btw. I made already some improvments, so the actual question may not fit with all answers (thanks for your help till now)

Finaly its now just about to integrate the filter "dont show people listed in table "hide" for my actual user".

Answer 1

I think you need something along these general lines. I've done it slightly different from your question. Instead of getting the top 10 then removing records. It gets the top 10 records which would not be hidden.

SELECT c.ID, c.fromuserid, c.roomid, c.text, u.nickname
FROM chats c
JOIN user u ON c.fromuserid = u.id
where c.roomid = 3 AND user.banned = 0
AND NOT EXISTS(
              SELECT * FROM hide h 
              WHERE h.hideuser = c.fromuserid
              AND orguser = $userid) 
ORDER BY c.ID DESC
LIMIT 0,10