Codeigniter/PHP Outer JOIN Syntax on Multiple Tables

Question

I am trying to perform an outer JOIN 3 tables based on the "site_id" column in each table. I'm using Codeigniter/Active Record.

The user enters a site number via a form and once the number is submitted it should call the model that executes something like this:

$site_id = $this->input->post('site_id');

$this
->db
->select('*')
->where('site_id', $site_id)
->from('sites')
->join('leader', 'sites.site_id = leader.site_id', 'outer')
->join('state', 'sites.site_id = state.site_id', 'outer');

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

However I get the following error message:

"Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN leader ON sites.site_id = leader.site_id OUTE' at line 3

SELECT * FROM (sites) OUTER JOIN leader ON sites.site_id = leader.site_id OUTER JOIN state ON sites.site_id = state.site_id WHERE site_id = '63'

Filename: /home2/cdowney/public_html/mclinbase.com/models/viewsite_model.php

Line Number: 33"

I believe(hope) this is just a small syntax error that I'm missing or a misuse of the outer join but I have not been able to figure it out.

Any guidance?

Your where clause is ambiguous, append the table name with the column name sites.site_id = '63' — Nil'z
– Nil'z, Commented Apr 24, 2014 at 15:03

Parag Tyagi · Accepted Answer · 2014-04-24 15:24:12Z

1

Try this -

$this->db->select('*');
$this->db->where('sites.site_id', $site_id);
$this->db->from('sites');
$this->db->join('leader', 'sites.site_id = leader.site_id', 'outer');
$this->db->join('state', 'sites.site_id = state.site_id', 'outer')

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

answered Apr 24, 2014 at 15:24

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa Over a year ago

Try this answers are low value on Stackoverflow because they do very little to educate/empower thousands of future researchers. Remember, you are not only answering this question for one person, you are also answering all future questions/OPs where this page is used as a duplicate closure link.

Nil'z · Accepted Answer · 2014-04-24 15:16:07Z

0

Simply union select the left and right join to get the same result as outer join:

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id

Maybe this will help.

answered Apr 24, 2014 at 15:16

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

Collectives™ on Stack Overflow

Codeigniter/PHP Outer JOIN Syntax on Multiple Tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related