Problem with JOIN

[eluser]ibnclaudius[/eluser]
I'm getting this error:

Error Number: 1054

Unknown column '2' in 'on clause'

SELECT * FROM (`schools` AS a) JOIN `users_schools` AS b ON `a`.`id` = `b`.`schools_id` JOIN `users` AS c ON `b`.`users_id` = `2`

when running this query:

Code:

$query = $this->db->select('*')
->from($this->schools_table . ' AS a')
->join('' . $this->users_schools_table . ' AS b', 'a.id = b.schools_id')
->join('' . $this->users_table . ' AS c', 'b.users_id = ' . $this->session->userdata('id') . '')
->get();

What am I doing wrong?

[eluser]scottwire[/eluser]
Your second join needs to have 2 columns check equality just like the first one and then your session id needs to be compared to in a where clause

Not sure if this is perfect, but something to this effect:

Code:

$query = $this->db->select('*')
->from($this->schools_table . ' AS a')
->join('' . $this->users_schools_table . ' AS b', 'a.id = b.schools_id')
->join('' . $this->users_table . ' AS c', 'b.users_id = c.users_id')
  ->where( 'b.users_id = ' . $this->session->userdata('id') );
->get();

[eluser]Bhashkar Yadav[/eluser]
can you print the actual query using

Code:

echo $this->db->last_query();

[eluser]InsiteFX[/eluser]
There is also a third parameter to the join left etc.

[eluser]ibnclaudius[/eluser]
I made some change:

Code:

$query = $this->db->select('*')
  ->from($this->schools_table . ' AS a')
  ->join('' . $this->users_schools_table . ' AS b', 'a.id = b.schools_id', 'INNER')
  ->join('' . $this->users_table . ' AS c', 'b.users_id = c.users_id', 'INNER')
  ->where( 'b.users_id = ' . $this->session->userdata('id') . '' )
  ->get();

but the error persist:

A PHP Error was encountered

Severity: Notice

Message: Use of undefined constant session - assumed 'session'

Filename: controllers/site.php

Line Number: 28

[eluser]goFrendiAsgard[/eluser]
have you load session library?

Code:

$this->load->library('session');

You should load it in your constructor or in your current function

[eluser]ibnclaudius[/eluser]
Removing the alias there is no error.

Code:

$this->db->select('*')
  ->from($this->users_table)
  ->join($this->users_schools_table, $this->users_schools_table . '.users_id = ' . $this->users_table . '.id', 'INNER')
  ->join($this->schools_table, $this->schools_table . '.id = ' . $this->users_schools_table . '.schools_id', 'INNER')
  ->where($this->users_table . '.id = ' . $this->session->userdata('id'))
  ->get();

Code:

SELECT * FROM (`users`) INNER JOIN `users_schools` ON `users_schools`.`users_id` = `users`.`id` INNER JOIN `schools` ON `schools`.`id` = `users_schools`.`schools_id` WHERE `users`.`id` = 999

I read some tutorials about JOIN, but I do not understand the concept very well.

Here are my tables:

Code:

CREATE  TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `type` INT NOT NULL DEFAULT '1' ,
  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) )
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;

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;

I want to select all fields of a user from users table and all fields of every school related to that user from schools table. What I have to change to fix my SELECT? :S

Another thing, how will I treat the results?

Thanks, all.

[eluser]goFrendiAsgard[/eluser]
May be you should use LEFT JOIN
Try to run this SQL :

Code:

SELECT users.*, schools.*
FROM
  users
LEFT JOIN users_schools ON users.id = user_schools.user_id
LEFT JOIN schools ON schools.id = user_schools.user_id;