mysql select and where over several tables (very tricky)

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

Although I can't immediately find a simple way to answer your question as-is, I can point you in the right direction:

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Using subqueries should enable you to go and select from both blocked and hidden tables, and using those in your original query.

Answer 2

Not tested but it would be something like:

$sql_com = "SELECT us.id, us.nickname, ch.text, ch.id ".
           " FROM  chats ch,  ".
           " user us, ".   
           " hide hi, ".
           " banned ba, ".
           " WHERE ".
           " us.id != hi.hideuser ".
           " us.id != ba.user ".
           " us.id = ch.fromuserid ".
           " AND ch.roomid = 3 ".
           " ORDER BY ch.id DESC LIMIT 0,10";

Answer 3

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