Welcome Guest, Not a member yet? Register   Sign In
join with multiple conditions
#1

[eluser]bkarat[/eluser]
Hi !

I am new to CodeIgniter. The following query style is working fine in CodeIgniter:

$query=$this->db->query("Select b.INVCODE, b.INVNAME, b.LESSTYPE, a.PROPOSEDAMT, a.INVAMT
from STD b left outer join EMP a
on (b.INVCODE=a.INVCODE) and (a.FINSHORT='$finyear') and
(a.EMPCODE='$user')");

I am not getting the exact syntax to put this in Active Record style. When I use the last two conditions in a where clause, the result is not correct.

Thanks in advance.

With regards,
Bala
#2

[eluser]darkhouse[/eluser]
That's because you don't have a where clause to begin with. Just put "(b.INVCODE=a.INVCODE) and (a.FINSHORT=’$finyear’) and (a.EMPCODE=’$user’)" in the second parameter of your $this->db->join();
#3

[eluser]bkarat[/eluser]
Thanks for yr reply. I am getting an error on this line -> "Parse error: syntax error, unexpected T_VARIABLE in ....."

My join looks like this now:

$this->db->join('EMP a','(b.INVCODE=a.INVCODE) and (a.FINSHORT='$finyear') and (a.EMPCODE='$user')','left outer');

With regards,
Bala
#4

[eluser]darkhouse[/eluser]
Looks like you need to escape your quotes, or just use double quotes to wrap your statements. Try this.

Code:
$this->db->join("EMP a", "(b.INVCODE=a.INVCODE) and (a.FINSHORT='$finyear') and (a.EMPCODE='$user')", "left outer");
#5

[eluser]bkarat[/eluser]
Hi !

Thanks a lot. It works now.

Thanks again.

With regards,
Bala
#6

[eluser]bretticus[/eluser]
Funny, I am using CI 1.7.1 and when I use the following line for multiple conditions on my join:

Code:
$this->db->join('ProductPhotos', '(Products.ProductID = ProductPhotos.ProductID) AND (Photos.Type = \'small\')', 'left');

I get an error because it produces this:

Code:
LEFT JOIN `ProductPhotos` ON `Products`.`ProductID` = `ProductPhotos`.`ProductID)` AND (Photos.Type = 'small')

It is automatically whacking off the first parenthesis.

Anybody have a similar problem?
#7

[eluser]bretticus[/eluser]
I got rid of the parens and ordered my joins differently and it worked.
#8

[eluser]darkhouse[/eluser]
yeah you definitely didn't need any parenthesis in that statement.
#9

[eluser]bretticus[/eluser]
[quote author="darkhouse" date="1238051365"]yeah you definitely didn't need any parenthesis in that statement.[/quote]

Right, I didn't pay attention the very first time (no parens) to the real database error and took myself on a wild goose hunt. Smile




Theme © iAndrew 2016 - Forum software by © MyBB