• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
join not returning all rows

#1
[eluser]mike34666[/eluser]
i have 3 tables involved in this query. The first is an entry table which can have 1 or more users associated with it by the entry_user table. Then I have the user table which holds the user info. The following sql command works.

Code:
select entry.id, user.first_name
from entry
left join entry_user on entry.id = entry_user.entry_id
left join user on entry_user.user_id = user.id
where entry.id = 9;

in this example the entry table is associated with 2 users. so it returns:

+----+------------+
| id | first_name |
+----+------------+
| 9 | David |
| 9 | Jonny |
+----+------------+

But when I try and use the codeigniter object oriented method it only returns 1 row, ie 1 name. here is the code i tried:

Code:
$this->db->select('entry.id AS entry_id, user.first_name AS first_name');
$this->db->from('entry');
$this->db->join('entry_user', 'entry_user.id = entry.id', 'left');
$this->db->join('user', 'entry_user.user_id = user.id', 'left');
$this->db->where('entry.id = 9');
$query = $this->db->get();

I know that I can put the straight sql command in the get command but I want to use the object oriented method if possible for clean code. can anybody tell me what I am doing wrong? thanks.


Messages In This Thread
join not returning all rows - by El Forum - 09-26-2009, 10:19 PM
join not returning all rows - by El Forum - 09-27-2009, 12:33 AM
join not returning all rows - by El Forum - 09-27-2009, 03:03 AM
join not returning all rows - by El Forum - 09-27-2009, 07:09 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.