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