RIGHT OUTER JOIN not returning data from both tables in CodeIgniter
When debugging a query that appears to return missing columns, it is tempting to blame the database engine or assume the RIGHT OUTER JOIN syntax is incorrect. In your specific case using CodeIgniter's Active Record, the data structure reveals the real culprit: the join condition logic combined with how null values are handled in the result set.
You have defined the join on tblanswers.answerid = credentials.cid. Let's analyze the execution flow. You select from tblanswers and perform a right outer join to credentials. A right outer join guarantees that all rows from the right table (credentials) are included, even if there is no matching row in the left table (tblanswers).
Looking at your debug output, every record contains a cid, a name, and an email. This indicates that the database successfully retrieved rows from the credentials table. However, all records share the same answerid, userid, and questionid fields populated with empty strings or missing values in your print dump.
The issue lies in the join direction relative to your primary key constraints. If your tblanswers table has a foreign key relationship pointing to credentials.cid, then every row in tblanswers should logically have a matching cid. When you run a RIGHT OUTER JOIN where the right table is the reference (parent) of the relationship, and there are no orphan records in credentials (i.e., every cid exists in tblanswers), the query effectively behaves like an INNER JOIN.
If your goal is to list all credentials and their associated answers, but some credentials have no answers yet, a right join is theoretically correct. However, if you are seeing duplicate credential data or missing answer data, check the cardinality of your data. In your dump, rows 0 through 4 show different cid values (83 through 87) but identical empty fields for the answer columns. This suggests that the join matched every credential row, but the tblanswers table simply lacks records for those specific IDs at this moment.
To verify if this is the intended behavior or a data integrity issue, you must inspect the raw SQL generated by CodeIgniter. The Active Record class might be stripping out null values depending on your configuration, but more often, it is a misunderstanding of which side of the join defines the "master" list.
If you intend to display all answers and only include credentials where they exist, an INNER JOIN is the standard approach because it filters out the nulls automatically. If you strictly need all credentials regardless of whether they have answers, ensure your application logic handles the null answerid gracefully. In your view loop, checking for empty strings or nulls before echoing $row->answerA will prevent blank lines from appearing unexpectedly.
Another common pitfall in CodeIgniter 2.x (implied by the syntax) is how the join method handles duplicates. If a single credential ID maps to multiple answers, a right join will return multiple rows for that single credential, which might look like data duplication if you are not aggregating correctly. Conversely, if a credential has no answers, the answer columns will be null, which matches your output where answerid is empty.
Consider switching to an explicit SQL string or using the query builder's where_null() checks if you need to distinguish between "no data returned by join" and "data explicitly set to null". For instance:
$this->db->select('tblanswers.*, credentials.*');
$this->db->from('tblanswers');
$this->db->join('credentials', 'tblanswers.answerid = credentials.cid', 'right outer');
If you find that the cid is present in every row, your join condition is working as a filter. If you want to exclude rows where the answer data is missing, you might prefer:
$this->db->join('credentials', 'tblanswers.answerid = credentials.cid', 'inner');
This change forces the database to only return rows where a match exists in both tables, effectively cleaning up your dataset if orphaned credentials are not a requirement for your view. Always validate that your cid in tblanswers actually references existing entries in credentials to avoid confusion between join logic and data sparsity.
