title: MySQL Database JOIN tables and Search WHERE <multiple-columns>
body: <p>I have 3 tables <strong>tweet</strong>, <strong>room</strong>, <strong>room_tweet</strong>:</p>
  
  <pre><code>       tweet                    room                          room_tweet
  +----+------+-----+     +----+------+-----+     +----+---------+----------+----------+
  | id | text | ... |     | id | name | ... |     | id | room_id | tweet_id | disabled |
  +----+------+-----+     +----+------+-----+     +----+---------+----------+----------+
  | 10 | a... | ... |     | 20 | a... | ... |     | 80 |   20    |    10    |     1    |
  | 11 | b... | ... |     | 21 | b... | ... |     | 81 |   20    |    11    |     0    |
  | 12 | c... | ... |     | 22 | c... | ... |     | 82 |   21    |    10    |     1    |
  | 13 | d... | ... |     +----+------+-----+     | 83 |   20    |    12    |     1    |
  +----+------+-----+                             | 84 |   22    |    10    |     0    |
                                                  | 85 |   21    |    11    |     1    |
                                                  +----+---------+----------+----------+
  </code></pre>
  
  <p>I need a query that gets all <strong>tweets</strong> and show the <strong>disabled</strong> state in a specific <strong>room</strong>. </p>
  
  <p>All <strong>tweets</strong> are displayed in all <strong>rooms</strong>, each <strong>tweet</strong> has a <strong>disabled</strong> state and is different on each <strong>room</strong> (same <strong>tweet</strong> has a different <strong>disabled</strong> state in each <strong>room</strong>) this is achieved with the <strong>room_tweet</strong> table.</p>
  
  <p>This is the query I've written so far:</p>
  
  <pre><code>SELECT t.id AS tweet_id, t.text AS tweet, rt.disabled, r.id AS room_id, r.name AS room  
  FROM tweet as t 
  LEFT JOIN room_tweet as rt ON t.id = rt.tweet_id 
  LEFT JOIN room as r ON rt.room_id = r.id
  WHERE r.id = 21 OR r.id IS NULL
  </code></pre>
  
  <p>Not all <strong>tweets</strong> are in the <strong>room_tweet</strong> table to associate them to a <strong>room</strong>. Therefore many <strong>tweets</strong> will have a <strong>NULL</strong> reference to a <strong>room</strong> in the <strong>result set</strong>, and the same goes for the <strong>room</strong>, some <strong>rooms</strong> will have no <strong>tweets</strong> associated to them. [This does not mean that the <strong>tweet</strong> should not appear on the <strong>result set</strong>.] </p>
  
  <p>All <strong>tweets</strong> should appear in the <strong>result set</strong> once (ONLY ONE TIME) with a <strong>disabled</strong> state of [1, 0 or NULL].</p>
  
  <p>It would be a simple query if all <strong>tweets</strong> happened to have an association on the <strong>room_tweet</strong> table, but this is not the case. Since <strong>tweets</strong> are created throughout the day and <strong>rooms</strong> are created randomly the <strong>room_tweet</strong> table will not, and should not have all associations. </p>
  
  <pre><code>Result without WHERE:
  +----------+-------+----------+---------+------+
  | tweet_id | tweet | disabled | room_id | room |
  +----------+-------+----------+---------+------+
  |    10    | a...  |    1     |    20   | a... |
  |    10    | a...  |    1     |    21   | b... |
  |    10    | a...  |    0     |    22   | c... |
  |    11    | b...  |    0     |    20   | a... |
  |    11    | b...  |    1     |    21   | b... |
  |    12    | c...  |    1     |    20   | a... |
  |    13    | d...  |   NULL   |   NULL  | NULL |
  +----------+-------+----------+---------+------+
  Tweets are duplicated multiple times ex: tweet 10 shows 3 times.
  
  Result with (WHERE r.id = 21 OR r.id IS NULL)
  +----------+-------+----------+---------+------+
  | tweet_id | tweet | disabled | room_id | room |
  +----------+-------+----------+---------+------+
  |    10    | a...  |    1     |    21   | b... |
  |    11    | b...  |    1     |    21   | b... |
  |    13    | d...  |   NULL   |   NULL  | NULL |
  +----------+-------+----------+---------+------+    
  Tweets are missing!, only tweet with id 10, 11 and 13 show up. This is because 
  the other tweets have been associated with other rooms and therefore are not 
  **NULL** anymore. (All tweets should appear once).
  </code></pre>
  
  <p>As you can see on the <strong>result set</strong>, not all <strong>tweets</strong> are being displayed (when <strong>WHERE</strong> is being used) and the other problem is that <strong>tweets</strong> that have been associated with multiple <strong>rooms</strong> on the <strong>room_tweet</strong> table, therefore they are being duplicated on the <strong>result set</strong>. (I only want to see each <strong>tweet</strong> once!)</p>
  
  <p>So the question is: What am I missing on the <strong>query</strong> and is it even possible to get the <strong>result</strong> that I'm expecting? </p>
  
  <p>Is it possible to fix this behavior? if so, is my query the problem or are the tables wrong?</p>
  
  <p>If you want to test the queries here is the SQL to create all tables and data in phpMyAdmin:</p>
  
  <pre><code>CREATE TABLE `room` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(60) NOT NULL, `...` varchar(3) NOT NULL DEFAULT '...', PRIMARY KEY (`id`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;
  CREATE TABLE `tweet` (`id` int(11) NOT NULL AUTO_INCREMENT, `text` varchar(140) NOT NULL, `...` varchar(3) NOT NULL DEFAULT '...', PRIMARY KEY (`id`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
  CREATE TABLE `room_tweet` (`id` int(11) NOT NULL AUTO_INCREMENT, `room_id` int(11) NOT NULL, `tweet_id` int(11) NOT NULL, `disabled` tinyint(1) NOT NULL, PRIMARY KEY (`id`), KEY `fk_room_tweet_1` (`room_id`), KEY `fk_room_tweet_2` (`tweet_id`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=86 ;
  ALTER TABLE `room_tweet` ADD CONSTRAINT `fk_room_tweet_1` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`), ADD CONSTRAINT `fk_room_tweet_2` FOREIGN KEY (`tweet_id`) REFERENCES `tweet` (`id`);    
  INSERT INTO `room` (`id`, `name`, `...`) VALUES (20, 'a...', '...'), (21, 'b...', '...'), (22, 'c...', '...');    
  INSERT INTO `tweet` (`id`, `text`, `...`) VALUES (10, 'a...', '...'), (11, 'b...', '...'), (12, 'c...', '...'), (13, 'd...', '...');
  INSERT INTO `room_tweet` (`id`, `room_id`, `tweet_id`, `disabled`) VALUES (80, 20, 10, 1), (81, 20, 11, 0), (82, 21, 10, 1), (83, 20, 12, 1), (84, 22, 10, 0), (85, 21, 11, 1);
  </code></pre>
  
  <p>I've tried JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, and noon seem to fix my problem. </p>
  
  <p>Thanks for your time, and once again, the only thing I need is a <strong>result set</strong> that gets all <strong>tweets</strong> that show the <strong>disabled</strong> state on that specific <strong>room</strong>. If it doesn't have a <strong>disabled</strong> state <strong>NULL</strong> should appear.</p>
  
  <p>So far the best Result has been this:</p>
  
  <pre><code>SELECT t.id AS tweet_id, t.text AS tweet, rt.disabled, r.id AS room_id, r.name AS room  
  FROM tweet as t 
  LEFT JOIN room_tweet as rt ON t.id = rt.tweet_id 
  LEFT JOIN room as r ON rt.room_id = r.id
  </code></pre>
  
  <p>Once again thanks for your time.</p>