查詢僅返回連接表中的匹配記錄。我究竟做錯了什么？我試圖顯示代理表中的所有記錄，無論貸款表中是否有匹配的記錄。也許我想要實現的目標的邏輯是錯誤的。 $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 |
3 回答
翻過高山走不出你
TA貢獻1875條經驗 獲得超3個贊
$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();
慕萊塢森
TA貢獻1810條經驗 獲得超4個贊
弄清楚了
$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);
弒天下
TA貢獻1818條經驗 獲得超8個贊
首先，據我所知，您將“標準查詢”與“查詢生成器”混合在一起，這是僅使用一個的好習慣（最好是查詢生成器，以防您切換到另一個數據庫引擎）。
同樣在第二個連接中，您正在進行“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（最終建議：每當您使用聯接時，請始終在查詢中使用符號，這樣更容易理解并避免兩個數據庫具有相同名稱的列時出現錯誤）
- 3 回答
- 0 關注
- 144 瀏覽
添加回答
舉報
0/150
提交
取消