Help With Join

[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?

[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.

[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);

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

EDIT: Nevermind, you noticed that. Haha.

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

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

[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:

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

[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?

[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.