Help With Join - Printable Version

Help With Join - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Help With Join (/showthread.php?tid=48708)

Help With Join - El Forum - 01-25-2012

[eluser]ibnclaudius[/eluser]
I have two tables:

Code:
CREATE  TABLE IF NOT EXISTS `schools` (

  `id` INT NOT NULL AUTO_INCREMENT ,

  `name` VARCHAR(45) NOT NULL ,

  `active` TINYINT(1) NOT NULL ,

  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,

  PRIMARY KEY (`id`) )

ENGINE = InnoDB;

Code:
CREATE  TABLE IF NOT EXISTS `users_schools` (

  `users_id` INT NOT NULL ,

  `schools_id` INT NOT NULL ,

  `active` TINYINT(1) NOT NULL ,

  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,

  PRIMARY KEY (`users_id`, `schools_id`) ,

  INDEX `fk_users_has_schools_schools1` (`schools_id` ASC) ,

  INDEX `fk_users_has_schools_users1` (`users_id` ASC) ,

  CONSTRAINT `fk_users_has_schools_users1`

    FOREIGN KEY (`users_id` )

    REFERENCES `meuboletim`.`users` (`id` )

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `fk_users_has_schools_schools1`

    FOREIGN KEY (`schools_id` )

    REFERENCES `meuboletim`.`schools` (`id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

I want to select every school from 'schools' where 'schools'.'id' = 'users_schools'.'schools_id' and 'users_schools'.'users_id' = $this->session->userdata('id')

I'm trying to do like this:

Code:
$query = $this->db->select('*')->from($this->schools_table)

  ->join($this->users_schools_table, $this->users_schools_table . 'users_id = ' . $this->session->userdata('id'))

  ->join($this->users_schools_table, $this->users_schools_table . 'schools_id = ' . $this->schools_table . 'id')

  ->get();

What am I doing wrong?

Help With Join - El Forum - 01-25-2012

[eluser]Aken[/eluser]
You're missing the periods connecting your table names and columns.

Code:
$query = $this->db->select('*')->from($this->schools_table)

  ->join($this->users_schools_table, $this->users_schools_table . '.users_id = ' . $this->session->userdata('id'))

  ->join($this->users_schools_table, $this->users_schools_table . '.schools_id = ' . $this->schools_table . '.id')

  ->get();

I'm not sure if the joins are 100% correct, but I noticed that right away. Give it a shot.

By the way, you can use $this->db->last_query() to see what the attempted query looks like, and what might be wrong.

Help With Join - El Forum - 01-25-2012

[eluser]CroNiX[/eluser]
try

Code:
$query = $this->db

  ->join($this->users_schools_table, $this->users_schools_table . '.users_id = ' . $this->session->userdata('id').' AND '.$this->users_schools_table . '.schools_id = ' . $this->schools_table . '.id')

  ->get($this->schools_table);

Help With Join - El Forum - 01-25-2012

[eluser]Aken[/eluser]
You missed the periods, too, CroNIX Wink

EDIT: Nevermind, you noticed that. Haha.

Help With Join - El Forum - 01-25-2012

[eluser]CroNiX[/eluser]
Yeah, I did. And your code is overly verbose Smile

Muhahah.

Help With Join - El Forum - 01-25-2012

[eluser]Aken[/eluser]
Yeah, I honestly didn't look over the JOIN structure, just noticed the missing periods. Big Grin

Help With Join - El Forum - 01-26-2012

[eluser]ibnclaudius[/eluser]
This code:

Code:
$query = $this->db->select('*')->from($this->schools_table)->join($this->users_schools_table, $this->users_schools_table . '.users_id = ' . $user_id . ' AND ' . $this->users_schools_table . '.schools_id = ' . $this->schools_table . '.id')->get();

is giving this error:

Quote:SELECT * FROM (`schools`) JOIN `users_schools` ON `users_schools`.`users_id` = `4` AND users_schools.schools_id = schools.id

Help With Join - El Forum - 01-26-2012

[eluser]ibnclaudius[/eluser]
Fixed with

Code:
$query = $this->db->select('*')->from($this->schools_table)->join($this->users_schools_table, $this->users_schools_table . '.schools_id = ' . $this->schools_table . '.id')->where($this->users_schools_table . '.users_id', $user_id)->get();

Shouldnt I use DISTINCT, GROUP BY or something like that?

Help With Join - El Forum - 01-26-2012

[eluser]Faisal Alghamdi[/eluser]
it will be easy with active record and something like this. ..

Code:
$this->db->select('*');

   $this->db->select_avg('SALARY');

   $this->db->from('main_records');   $this->db->where('main_records.approval',1);  

   $this->db->join('names', 'names.idnm = main_rs.idnm','left');

   $this->db->join('ages', 'ages.idEXPERIENCE = main_records.idEXPERIENCE','left');

   $this->db->group_by('CITY');

   $this->db->order_by('SALARY','asc');

   $q = $this->db->get();

return $q->result();

using active records is the best way in terms of debugging the code.