+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: Cross Join (/showthread.php?tid=1282)
Cross Join - juggernautsei - 02-25-2015
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 VaccineInstead it returns literally a thousand rows.
Anyone know what the code should be to cross join three tables?
RE: Cross Join - Rufnex - 02-25-2015
On your joins you need the condition as second parameter as described here
http://www.codeigniter.com/userguide3/database/query_builder.html?highlight=join
As e.g. like this
$this->db->join('children as b', 'a.id = b.a_id');
RE: Cross Join - edellascenza - 02-25-2015
When you JOIN a table without any condition, it will bring ALL the dataset within it.
RE: Cross Join - aurelien - 02-25-2015
Hi,
Be careful, a CROSS JOIN returns all rows without filter (immun rows * immun_master rows * children).
Agree with Rufnex.
RE: Cross Join - juggernautsei - 02-25-2015
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;
}