Original table view
1. Apply inner join with it
After the INNER JOIN, the some conditions such as state = 1 no longer applies.
Then, table view will be:
The pagination is displayed for an unknown reason despite having only three records.
2. The first step in debugging is to check the INNER JOIN query conditions.
Then refer to the table's field:
Table #__dt_sub_commission (Sales No are unique)
Table #__dt_sub_commission_history (When the commission is co-broke, there will be two records for two agents, making the sales number non-unique.)
The solution is to select the sales no before the INNER JOIN and use DISTINCT on it.
This allows you to INNER JOIN with the other table's non-unique data without affecting the default query logic.
Remark: If you want, you also can select the other field from #__dt_sub_commission_history, such as
->select('DISTINCT e.sales_no, e.commission_type')
select unique sales no and commission_type.