Cross Join

Hi,

I have been having a problem with getting cross join to function properly in my project.
I need to cross join three tables to produce display results.

The model code is this:

Code:

function immChild() {
   
   $this->db->select('a.id, a.child, a.immune, a.immun_date, b.id, b.fname, b.lname, c.id, c.name');
$this->db->join('immun_master as c','true');
$this->db->join('children as b','true');
$query = $this->db->get('immun as a')->result();
               
return $query;
}

This query should return on

Code:

id  child  immune  immun_date  id  fname   lname  id  name  
1   1001    2      2011-04-23 1001 Johny    Jame   2  Swine Flu Vaccine
2   1001    3      2011-04-30 1001 Johny    Jame   3  Bird Flu Vaccine
3   1002    6      2011-04-30 1002 Chelsea  James  6  Hepatitis B
4   1002    5      2011-04-29 1002 Chelsea  James  5  Measles Vaccine
5   1003    1      2011-01-06 1003 Charles  Jacob  1  H1N1 Vaccine
6   1005    3      2010-10-04 1005 Hansome  Little 3  Bird Flu Vaccine
7   1231    3      2014-08-01 1231 Jennifer Ylanan 3  Bird Flu Vaccine

Instead it returns literally a thousand rows.
Anyone know what the code should be to cross join three tables?

On your joins you need the condition as second parameter as described here

http://www.codeigniter.com/userguide3/da...light=join

As e.g. like this

$this->db->join('children as b', 'a.id = b.a_id');

When you JOIN a table without any condition, it will bring ALL the dataset within it.

Hi,

Be careful, a CROSS JOIN returns all rows without filter (immun rows * immun_master rows * children).

Agree with Rufnex.

You are absolutely right.

I forgot the condition. Now it works right.

Code:

function immChild() {
   
   $this->db->select('c.id, c.name, b.id, b.fname, b.lname, a.id, a.child, a.immune, a.immun_date');
$this->db->join('immun_master as c', 'c.id = a.immune','true');
$this->db->join('children as b', 'a.child = b.id', 'true');
$query = $this->db->get('immun as a')->result();
       
return $query;
}