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（最终建议：每当您使用联接时，请始终在查询中使用符号，这样更容易理解并避免两个数据库具有相同名称的列时出现错误）