MySQL用每个表中的where子句连接三个表

Question

我有三个表，我需要将所有表连接在一起，并创建一个包含where条件的大表。

我正在使用MySQL后端的Codeigniter。

考虑一个HTML表单，其中要求管理员输入项目ID，然后显示一个表，其中列出了数据库中具有该特定ID的所有参与者。

考虑一个存储有关玩家信息的表，名为" players "：

userid   | username   | acct_id
 ---------------------------------
 5001     | user1      | 400000
 5002     | user2      | 400000
 5003     | user3      | 400001
 5004     | user4      | 400002
 5005     | user5      | 400003

考虑存储用户库存信息的第二个表，名为“库存”：

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

考虑第三个表，该表存储关于用户“保险库”的信息，名为"vault_inventory"：

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

考虑用户，使用上面的表单输入项目ID "406“。将显示具有下列信息的表：

        |            |           | 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

我正在使用Codeigniter，所以在我的模型中，到目前为止，我已经尝试了大多数where、left、right或inner联接：

$this->db->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->db->from('players');
 $this->db->join('inventory', "players.userid = inventory.userid", 'left');
 $this->db->join('vault_inventory', "players.userid = vault_inventory.userid");
 $this->db->where('inventory.item_id', $itemID);
 $this->db->or_where('vault_inventory.item_id', $itemID);
 $q_playerList = $this->db->get();
 $return_playerList = $q_playerList->result_array();     

上述查询产生的随机结果很难解释或说明。基本上，数据似乎正在被复制，以便在vault_inventory表中为该用户生成随机结果(换句话说，正在搜索inventory.item_id，但它似乎完全忽略了vault_inventory.item_id，而是在那里填充随机内容。当库存表包含一个条目但vault_inventory不包含时，这一点尤其明显。不管怎样，这肯定不对。

如果我用：

$this->db->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->db->from('players');
 $this->db->join('inventory', "players.userid = inventory.userid", 'left');
 $this->db->join('vault_inventory', "players.userid = vault_inventory.userid");
 $this->db->where('inventory.item_id', $itemID);
 $this->db->where('vault_inventory.item_id', $itemID);
 $q_playerList = $this->db->get();
 $return_playerList = $q_playerList->result_array();

上述查询不会产生任何结果。

在我看来，我正在生成一张表：

<tbody>
    <?php foreach ($playerList as $playerItem): ?>
     <tr>
      <td><?php echo $playerItem['userid']; ?></td>
      <td><?php echo $playerItem['username']; ?></td>
      <td></td>
      <td></td>
      <td><?php echo $playerItem['acct_id']; ?></td>
      <td><?php echo $playerItem['inv_amount']; ?></td>
      <td><?php echo $playerItem['vault_amount']; ?></td>
      <td><?php echo $playerItem['inv_amount'] + $playerItem['vault_amount']; ?></td>
      <td></td>
     </tr>
    <?php endforeach; ?>
 </tbody>

因此，基本上，我需要一个查询，以显示在他们的库存和/或保险库中有指定ID的玩家列表。如果两个表都不包含任何条目，则将用户排除在结果之外。如果其中一个表有一个条目，但另一个没有，则显示0。每个用户1行，其中一个表或两个表中都有一个条目

相应的表将不会有“0”作为金额-如果播放机在该ID的该位置没有任何项目，或者删除/删除它们，则该表中的条目将被删除或永远不存在。

谢谢你能在我的问题上提供任何帮助。

Answer 1

根据沃里克的建议，我进入了MySQL工作台，用连接解决了一些问题，我想我终于得到了它。

查询的结果是

SELECT `players`.`userid`,`players`.`username`,`players`.`acct_id`,`inventory`.`id` AS `inv_id`,`inventory`.`item_id` AS `inv_item_id`,`inventory`.`amount` AS `inv_amt`,`vault_inventory`.`id` AS `vault_id`,`vault_inventory`.`item_id` AS `vault_item_id`,`vault_inventory`.`amount` AS `vault_amt`
FROM `players`
LEFT JOIN `inventory` ON `inventory`.`userid` = `players`.`userid` AND `inventory`.`item_id` = 601
LEFT JOIN `vault_inventory` ON `vault_inventory`.`userid` = `players`.`userid` AND `vault_inventory`.`item_id` = 601
WHERE `inventory`.`item_id` IS NOT NULL
OR `vault_inventory`.`item_id` IS NOT NULL
ORDER BY `players`.`userid` ASC