Codeigniter 查询未返回左表中的所有记录。只返回匹配的记录

Question

查询仅返回连接表中的匹配记录。我究竟做错了什么？我试图显示代理表中的所有记录，无论贷款表中是否有匹配的记录。也许我想要实现的目标的逻辑是错误的。

    $select = "agents.person_id, CONCAT(people.first_name, ' ', people.last_name) as last_name, SUM(loans.referral_amount) as referral_amount, COUNT( DISTINCT loans.customer_id ) as no_customers";

            $this->db->select($select, false);

            $this->db->from('agents');

            $this->db->join('people', 'agents.person_id=people.person_id', 'LEFT');

            $this->db->join('loans', 'agents.person_id=loans.referral_agent_id AND loans.loan_status = "paid" AND loans.delete_flag = 0', 'LEFT');

            $this->db->where('agents.deleted', 0);

    return $this->db->get();

Table: agents

+-----------+---------+--+

| person_id | deleted |  |

+-----------+---------+--+

|         1 |       0 |  |

|         2 |       0 |  |

|         3 |       1 |  |

|         4 |       0 |  |

+-----------+---------+--+

Table: loans

|   | loan_status | referral_amount | referral_agent_id | delete_flag|customer_id |

|---|-------------|-----------------|-------------------|-------------|-------------|

|   | paid        | 10              | 1                 | 0           | 2           |

|   | pending     | 20              | 1                 | 0           | 2           |

|   | approved    | 30              | 3                 | 1           | 1           |

Table: people

| person_id | first_name | last_name |

|-----------|------------|-----------|

| 1         | Test       | Ken       |

| 2         | Lorem      | Ipsum     |

| 3         | Stack      | Over      |

The result I am getting

| name     | referral amount | no of customers |

|----------|-----------------|-----------------|

| Test Ken | 10              | 1               |

What I am expecting

| name        | referral amount | no of customers |

|-------------|-----------------|-----------------|

| Test Ken    | 10              | 1               |

| Lorem Ipsum | null            | null            |

| Stack Over  | null            | null            |

Answer 1

$select = "agents.person_id, CONCAT(people.first_name, ' ', people.last_name) as last_name, SUM(loans.referral_amount) as referral_amount, COUNT( DISTINCT loans.customer_id ) as no_customers, people.phone_number";$this->db->select($select, false);$this->db->from('agents');$this->db->join('people', 'agents.person_id = people.person_id', 'LEFT');$this->db->join('loans', 'agents.person_id = loans.referral_agent_id', 'LEFT');$this->db->where('loans.loan_status', "paid");$this->db->where('loans.delete_flag', 0);$this->db->where('agents.deleted', 0);$result = $this->db->get();

Answer 2

弄清楚了$select = "c19_agents.person_id, referral_sum.user_referral_amount, CONCAT(c19_people.first_name, ' ', c19_people.last_name) as agents_name, customer_count.no_customers, referral_sum.user_referral_amount, phone_number";        $this->db->select($select, false);        $this->db->from('agents');        $this->db->join('people', 'agents.person_id=people.person_id', 'LEFT');        $this->db->join('(SELECT c19_loans.referral_agent_id, SUM(c19_loans.referral_amount)                   as user_referral_amount                   FROM c19_loans                       WHERE c19_loans.delete_flag = 0 AND c19_loans.loan_status = "paid"                       GROUP BY c19_loans.referral_agent_id) referral_sum', 'c19_agents.person_id = referral_sum.referral_agent_id', 'LEFT');        $this->db->join('(SELECT c19_loans.referral_agent_id, COUNT( DISTINCT c19_loans.customer_id)                    as no_customers                    FROM c19_loans                        WHERE c19_loans.delete_flag = 0 AND customer_id > 0                        GROUP by c19_loans.referral_agent_id) customer_count', 'c19_agents.person_id = customer_count.referral_agent_id', 'LEFT');        $this->db->where('agents.deleted', 0);

Answer 3

首先，据我所知，您将“标准查询”与“查询生成器”混合在一起，这是仅使用一个的好习惯（最好是查询生成器，以防您切换到另一个数据库引擎）。同样在第二个连接中，您正在进行“AND”比较，尽管这是有效的，但您可以尝试首先使连接起作用。我认为现在可以正常工作，但请确保调试您的查询打印结果并根据文档修复它，$select = "agents.person_id, CONCAT(people.first_name, ' ', people.last_name) as last_name, SUM(loans.referral_amount) as referral_amount, COUNT( DISTINCT loans.customer_id ) as no_customers, people.phone_number";$this->db->select($select, false);$this->db->from('agents');$this->db->join('people', 'agents.person_id=people.person_id', 'LEFT');$this->db->join('loans', 'agents.person_id=loans.referral_agent_id', 'LEFT'); $this->db->where('loans.loan_status', 'paid');$this->db->where('loans.delete_flag', 0);$this->db->where('agents.deleted', 0);$this->db->get();print_r($this->db->last_query());database.column（最终建议：每当您使用联接时，请始终在查询中使用符号，这样更容易理解并避免两个数据库具有相同名称的列时出现错误）