CI and in the join on

Question

$this->default->join('db D', 'C.col1 = D.col1 AND D.col2 = "MAIN"', 'LEFT');

I am getting 500 error on this join in CI but when i only use

$this->default->join('db D', 'C.col1 = D.col1', 'LEFT');

query is ok.

How to do join in CI with and in the ON part of the join?

FYI

1. Tried the entire query in MS SQL Server Management Studio and it runs ok.
2. Putting D.col2 = "MAIN" in where clause also works

Answer 1

What you have done should work but there are two things you could try to see if the outcome is better.

Test1: Which just exchanges where double and single quotes are used.

$this->default->join('db D', "C.col1 = D.col1 AND D.col2 = 'MAIN'", 'LEFT');

Test 2: Which turns off escaping the values and identifiers for the join call

this->default->join('db D', 'C.col1 = D.col1 AND D.col2 = "MAIN"', 'LEFT', FALSE);