title: MySQL join three tables with where clauses from each
body: <p>I have three tables that I need to join all together and create a large table with where conditions.</p>
  
  <p>I am using Codeigniter with MySQL backend.</p>
  
  <p>Consider an HTML form where the admin is asked to input the item ID. A table is then to display that lists all the players in the database with that specific ID.</p>
  
  <p>Consider a table that stores the information regarding players, named "players":</p>
  
  <pre><code>userid   | username   | acct_id
   ---------------------------------
   5001     | user1      | 400000
   5002     | user2      | 400000
   5003     | user3      | 400001
   5004     | user4      | 400002
   5005     | user5      | 400003
  </code></pre>
  
  <p>consider the second table that stores information about a user's inventory, named "inventory":</p>
  
  <pre><code>id  | userid  | item_id   | amount
   ------------------------------------
   1   | 5001    | 406       | 4
   2   | 5001    | 548       | 53
   3   | 5004    | 406       | 32
   4   | 5002    | 584       | 21
   5   | 5004    | 423       | 1
   6   | 5002    | 406       | 93
  </code></pre>
  
  <p>consider a third table that stores information about a user's "vault", named "vault_inventory":</p>
  
  <pre><code>id  | userid  | item_id   | amount
   ------------------------------------
   1   | 5004    | 548       | 4
   2   | 5002    | 406       | 29
   3   | 5005    | 406       | 43
   4   | 5003    | 432       | 54
   5   | 5001    | 548       | 12
  </code></pre>
  
  <p>Consider the user, using the form above, inputs Item ID "406". A table will display that has the following information:</p>
  
  <pre><code>        |            |           | qty in      | qty in    |        
   userid | username   | acct_id   | inventory   | vault     | total qty
   ------------------------------------------------------------------------
   5001   | user1      | 400000    | 4           | 0         | 4
  5002    | user2      | 400000    | 93          | 29        | 122
  5004    | user4      | 400002    | 32          | 0         | 32
  5005    | user5      | 400003    | 0           | 43        | 43
  </code></pre>
  
  <p>I am using Codeigniter, so in my model so far I have this, which I've tried with a vast majority of where  OR  or left, right or inner joins:</p>
  
  <pre><code>$this-&gt;db-&gt;select('inventory.userid,inventory.item_id,inventory.amount AS inv_amount,inventory.id,vault_inventory.userid,vault_inventory.item_id,vault_inventory.amount AS vault_amount,vault_inventory.id,players.userid AS userid2,players.acct_id,players.username');
   $this-&gt;db-&gt;from('players');
   $this-&gt;db-&gt;join('inventory', "players.userid = inventory.userid", 'left');
   $this-&gt;db-&gt;join('vault_inventory', "players.userid = vault_inventory.userid");
   $this-&gt;db-&gt;where('inventory.item_id', $itemID);
   $this-&gt;db-&gt;or_where('vault_inventory.item_id', $itemID);
   $q_playerList = $this-&gt;db-&gt;get();
   $return_playerList = $q_playerList-&gt;result_array();     
  </code></pre>
  
  <p>The above query produces random results which are hard to explain or illustrate. Basically it appears the data is getting duplicated to produce random results for that user in the vault_inventory table (in other words, the inventory.item_id is being searched, but it seems to be completely ignoring the vault_inventory.item_id and instead filling in random stuff there. This is especially evident
  when the inventory table contains an entry but the vault_inventory does not. Either way, it's definitely not correct.</p>
  
  <p>If I use:</p>
  
  <pre><code>$this-&gt;db-&gt;select('inventory.userid,inventory.item_id,inventory.amount AS inv_amount,inventory.id,vault_inventory.userid,vault_inventory.item_id,vault_inventory.amount AS vault_amount,vault_inventory.id,players.userid AS userid2,players.acct_id,players.username');
   $this-&gt;db-&gt;from('players');
   $this-&gt;db-&gt;join('inventory', "players.userid = inventory.userid", 'left');
   $this-&gt;db-&gt;join('vault_inventory', "players.userid = vault_inventory.userid");
   $this-&gt;db-&gt;where('inventory.item_id', $itemID);
   $this-&gt;db-&gt;where('vault_inventory.item_id', $itemID);
   $q_playerList = $this-&gt;db-&gt;get();
   $return_playerList = $q_playerList-&gt;result_array();
  </code></pre>
  
  <p>The above query produces no results.     </p>
  
  <p>I'm generating the table in my view:</p>
  
  <pre><code>&lt;tbody&gt;
      &lt;?php foreach ($playerList as $playerItem): ?&gt;
       &lt;tr&gt;
        &lt;td&gt;&lt;?php echo $playerItem['userid']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php echo $playerItem['username']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;/td&gt;
        &lt;td&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php echo $playerItem['acct_id']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php echo $playerItem['inv_amount']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php echo $playerItem['vault_amount']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php echo $playerItem['inv_amount'] + $playerItem['vault_amount']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;/td&gt;
       &lt;/tr&gt;
      &lt;?php endforeach; ?&gt;
   &lt;/tbody&gt;
  </code></pre>
  
  <p>so basically I need a query to display a list of players with the specified ID in their inventory and/or vault. If both the tables contain no entry, leave the user out of the results. If one of the tables has an entry, but the other does not, display 0. 1 row per user with an entry in either or both tables</p>
  
  <p>The respective table will never have a '0' as the amount - if the player does not have any items in that location of that ID, or deletes/removes them, the entry in that table will be deleted or never exist.</p>
  
  <p>Thanks for any help you can provide with my issue.</p>